What's New in EF Core 10: LeftJoin and RightJoin Operators in LINQ

If you've ever worked with databases, you know about LEFT JOIN (and conversely RIGHT JOIN). It's one of those things we use often, if not all the time. But in Entity Framework Core, doing a left join has always been... well, a bit of a pain.

I like joins that read like what they do. Unfortunately, until now, LINQ didn't have a straightforward way to express left/right joins. You had to jump through hoops with GroupJoin and DefaultIfEmpty, which made the code harder to read and maintain.

But .NET 10 finally fixes this with the brand new LeftJoin and RightJoin methods.

What's a LEFT JOIN (in plain words)?

A LEFT JOIN returns all rows from the left side and the matching rows from the right side. If there's no match, the right side is null. Why we use it: to keep "owners" even when they have no related rows (e.g., show all products even if some don't have a review).

The Old Way (GroupJoin + DefaultIfEmpty)

Before .NET 10, a left join in LINQ needed a group join (GroupJoin), then DefaultIfEmpty to keep left rows with no match. It worked, but the intent was buried in noise.

There are two ways you could write it: query syntax and method syntax.

Query syntax

var query =
    from product in dbContext.Products
    join review in dbContext.Reviews on product.Id equals review.ProductId into reviewGroup
    from subReview in reviewGroup.DefaultIfEmpty()
    orderby product.Id, subReview.Id
    select new
    {
        ProductId = product.Id,
        product.Name,
        product.Price,
        ReviewId = (int?)subReview.Id ?? 0,
        Rating = (int?)subReview.Rating ?? 0,
        Comment = subReview.Comment ?? "N/A"
    };

Here's the SQL generated by EF Core for the above query:

SELECT
    p."Id" AS "ProductId",
    p."Name",
    p."Price",
    COALESCE(r."Id", 0) AS "ReviewId",
    COALESCE(r."Rating", 0) AS "Rating",
    COALESCE(r."Comment", 'N/A') AS "Comment"
FROM "Products" AS p
LEFT JOIN "Reviews" AS r ON p."Id" = r."ProductId"
ORDER BY p."Id", COALESCE(r."Id", 0)

Method syntax

var query = dbContext.Products
    .GroupJoin(
        dbContext.Reviews,
        product => product.Id,
        review => review.ProductId,
        (product, reviewList) => new { product, subgroup = reviewList })
    .SelectMany(
        joinedSet => joinedSet.subgroup.DefaultIfEmpty(),
        (joinedSet, review) => new
        {
            ProductId = joinedSet.product.Id,
            joinedSet.product.Name,
            joinedSet.product.Price,
            ReviewId = (int?)review!.Id ?? 0,
            Rating = (int?)review!.Rating ?? 0,
            Comment = review!.Comment ?? "N/A"
        })
    .OrderBy(result => result.ProductId)
    .ThenBy(result => result.ReviewId);

Why this works: GroupJoin matches rows, DefaultIfEmpty inserts a single default (null) when no matches exist, so the left row still appears. We then flatten with SelectMany.

I think we can all agree that this is way too verbose for something as common as a left join.

The New Way in EF 10: LeftJoin

Now we can write what we mean. LeftJoin is first-class LINQ and EF Core translates it to a LEFT JOIN in SQL.

var query = dbContext.Products
    .LeftJoin(
        dbContext.Reviews,
        product => product.Id,
        review => review.ProductId,
        (product, review) => new
        {
            ProductId = product.Id,
            product.Name,
            product.Price,
            ReviewId = (int?)review.Id ?? 0,
            Rating = (int?)review.Rating ?? 0,
            Comment = review.Comment ?? "N/A"
        })
    .OrderBy(x => x.ProductId)
    .ThenBy(x => x.ReviewId)

The generated SQL is identical to the previous example.

Why this is better:

• Intent is clear: you see LeftJoin, you know what to expect.
• Less code, fewer moving parts (no GroupJoin, no DefaultIfEmpty, no SelectMany).
• Same result: all products kept, reviews may be null.

Also New: RightJoin

RightJoin keeps all rows from the right side and only matching rows from the left. EF Core translates it to RIGHT JOIN. It's handy when the "must keep" side is the second sequence.

Conceptually:

var query = dbContext.Reviews
    .RightJoin(
        dbContext.Products,
        review => review.ProductId,
        product => product.Id,
        (review, product) => new
        {
            ProductId = product.Id,
            product.Name,
            product.Price,
            ReviewId = (int?)review.Id ?? 0,
            Rating = (int?)review.Rating ?? 0,
            Comment = review.Comment ?? "N/A"
        });

Why use RightJoin: when your reporting starts from Reviews (keep all), and bring in matching Products if they exist.

Here's the generated SQL:

SELECT
    p."Id" AS "ProductId",
    p."Name",
    p."Price",
    COALESCE(r."Id", 0) AS "ReviewId",
    COALESCE(r."Rating", 0) AS "Rating",
    COALESCE(r."Comment", 'N/A') AS "Comment"
FROM "Reviews" AS r
RIGHT JOIN "Products" AS p ON r."ProductId" = p."Id"

Wrapping Up

Think about how often you need left joins. Showing all users with optional profile settings. All products with optional reviews. All orders with optional shipping info. It's everywhere!

Before, developers would sometimes skip the proper left join and do two separate queries instead. Or worse, they'd use an inner join and miss data. Now there's no excuse - it's just as easy as any other LINQ method.

A few quick tips when writing LINQ queries:

• In the projection, guard nullable side: review.Comment ?? "N/A"
• Keep projections small to avoid pulling more columns than needed
• Add indexes on the join keys for better query plans

That's it. With LeftJoin and RightJoin, the code finally matches the mental model.

See you next Saturday.