One-line left joins in EF Core 10 with LeftJoin

You want every customer, plus their orders where they have any. In SQL that's one LEFT JOIN. In LINQ it used to take three steps: a group join, a flatten, and DefaultIfEmpty so the customers with no orders survive. .NET 10 adds a LeftJoin operator and EF Core 10 translates it, so that query is one call now. My advice is to use it in new queries and leave your working ones alone. The SQL is the same either way, so a rewrite buys you a diff and nothing else.

The join you have been writing

Here is the old shape, in query syntax, over a customers table and an orders table.

from c in db.Customers
join o in db.Orders on c.Id equals o.CustomerId into matches
from o in matches.DefaultIfEmpty()
select new Row(c.Name, o.Code, (decimal?)o.Total);

Underneath, that's three calls: a group join, a flatten, and DefaultIfEmpty. The group join collects each customer's orders. The flatten turns those groups back into rows. DefaultIfEmpty keeps a customer whose group came out empty; drop it and you have an inner join.

The new form says it once.

db.Customers.LeftJoin(
    db.Orders,
    c => c.Id,
    o => o.CustomerId,
    (c, o) => new Row(c.Name, o!.Code, (decimal?)o!.Total));

It reads like a Join call, with the same tables, keys and projection. The one difference is in the types, and I come back to it further down.

The SQL both forms produce

I printed both queries with ToQueryString and compared the strings. They match, on SQLite and on SQL Server. The SQLite one:

SELECT "c"."Name", "o"."Code", "o"."Total"
FROM "Customers" AS "c"
LEFT JOIN "Orders" AS "o" ON "c"."Id" = "o"."CustomerId"

The rows match too. Four customers and three orders give five rows, and the two customers with no order come back with a null code and a null total. That's the whole reason I wouldn't rewrite a working query. Your database can't tell the two forms apart, so there's no faster query waiting at the end of the exercise. Clearer code is worth something while you're writing it. It isn't worth a regression on a query that already ships.

One thing to check before you reach for the new operator: a navigation may already do the work. Customer has an orders collection in this model, so from o in c.Orders.DefaultIfEmpty() gets the same five rows with no join clause and no keys to get wrong. Its SQL came out as the same string again. A hand-written left join is for the tables your model doesn't link, and that is where I'd spend the new operator.

When the customer has no order

The one real difference is in the C# types. LeftJoin declares the right side as nullable, so the result selector receives an Order?. With nullable reference types on, the compiler warns when you read a property off it. The old pattern types the same thing as a plain Order and says nothing, even though those two rows carry no order at all. I like the warning. It lands on the exact line where a missing order matters.

Reach past it without thinking and you get the first of these. Total is a plain decimal on the entity, and a decimal has nowhere to put a missing value.

// throws on the first customer with no order
(c, o) => o!.Total

// gives you null instead
(c, o) => (decimal?)o!.Total

The first line fails at run time, with InvalidOperationException: Nullable object must have a value. So my second piece of advice is about projections: type every column from the right side as nullable, or keep the order entity itself and let it arrive null. Either works. A cast costs you a few characters and saves a crash on the first customer without an order.

Versions, and query syntax

Two things have to line up. The operator belongs to .NET 10, so you need that runtime. The translation belongs to EF Core 10. On EF Core 9 the same query compiles and then throws, with The LINQ expression ... could not be translated. Worth knowing before you try it on an older project, because nothing warns you at build time.

The other limit is query syntax. C# 14 has no left join clause, so inside a from ... select query you still write the group join and DefaultIfEmpty. The EF Core team says as much in the release notes. If your code is query syntax throughout, the new operator means switching that one query to method calls, and mixing the two styles in a file has a small cost of its own.

RightJoin arrived at the same time and becomes a RIGHT JOIN. I reach for it rarely. Turning a left join around reads more clearly to most people, and in the demo it gives back the same five rows.

So: a new query on EF Core 10 gets LeftJoin. An old query that works gets left alone until you're in that file for another reason. The demo at github.com/gdhami-net/efcore10-leftjoin-demo runs both forms side by side.