LeftJoin

Write outer joins with the .NET 10 LeftJoin operator, which EF Core 10 translates to LEFT JOIN, and handle the null inner side correctly.

Signature

public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, TInner?, TResult>> resultSelector)

Added in .NET .NET 10 (System.Linq); translated by EF Core 10

Parameters

Parameter	Type	Description
`$outer`	`IQueryable<TOuter>`	The side whose every element appears in the result (the LEFT side).
`$inner`	`IEnumerable<TInner>`	The side that may be missing. In EF Core, another DbSet or query.
`$outerKeySelector`	`Expression<Func<TOuter, TKey>>`	Key from the outer element. Use an anonymous type for composite keys.
`$innerKeySelector`	`Expression<Func<TInner, TKey>>`	Key from the inner element. Must have the same type as the outer key, including anonymous type property names.
`$resultSelector`	`Expression<Func<TOuter, TInner?, TResult>>`	Builds each result. The inner argument is null for unmatched outer elements.

Return value

IQueryable<TResult>One result per matching outer/inner pair, plus one result with a null inner element for every outer element that has no match.

Errors

InvalidOperationException from EF Core when a key or result selector cannot be translated. NullReferenceException in LINQ to objects if the result selector dereferences the null inner element.

Changelog

PHP .NET 10 / EF Core 10LeftJoin and RightJoin added to Enumerable and Queryable; EF Core 10 translates both to SQL. Earlier versions needed GroupJoin, SelectMany and DefaultIfEmpty.

LeftJoin returns every element of the outer sequence, paired with each matching inner element, or with null when nothing matches. It is the outer-join counterpart of Join, added to LINQ in .NET 10, and EF Core 10 translates it to LEFT JOIN. RightJoin is the mirror image: every inner element, with a nullable outer.

Parameters

The two key selectors must produce the same type. For a composite key use anonymous types with the same property names and types on both sides: p => new { p.Id, Warehouse = "SYD" } and s => new { Id = s.ProductId, Warehouse = s.WarehouseCode }. Nullable and non-nullable keys do not match (int versus int?), so cast one side. The result selector's second argument is the nullable inner element.

Null handling depends on where the query runs

Selectors on IQueryable are expression trees, so C# does not allow ?. inside them. In SQL a missing row simply yields NULL columns, and EF Core accepts (decimal?)s.OnHand on a null s. The same lambda run in memory (LINQ to objects, a mocked IQueryable, or a client-evaluated final projection) throws NullReferenceException. Write the null check explicitly, s == null ? 0 : s.OnHand, and the expression is correct in both worlds; EF Core translates it to a CASE or COALESCE.

Choosing between join styles

Need	Use
Related data through a mapped navigation	The navigation in a `Select`; EF Core adds the join
Outer join between entities with no navigation (lookup tables, staging data, reports)	`LeftJoin`
Rows that have no partner (orphans, unsold products)	`LeftJoin` plus a `Where` on the null side, or `!Any(...)`
Code that must build on EF Core 9 or earlier	`GroupJoin` + `SelectMany` + `DefaultIfEmpty`

Prefer navigations when the relationship is in the model; LeftJoin is for joins the model does not describe. For "rows without a match", !db.Orders.Any(o => o.CustomerId == c.Id) becomes NOT EXISTS, which reads more clearly than filtering a left join for nulls and often plans the same.

Production failure modes

An inner side with several matches multiplies outer rows. A report that left-joins customers to orders and then sums a customer column counts it once per order.
Filtering the inner side in a Where after the join (.Where(x => x.Status == "Open")) drops the unmatched rows and turns the outer join into an inner join. Filter the inner sequence before passing it in.

The main example runs the operator over in-memory data through AsQueryable(), prints the expression tree EF Core would receive, and shows the null pitfall.

Example

Loading editor…

Press Run to execute the code.

When to use it

Listing every product with its Sydney warehouse stock, showing 0 for products that have no stock row there
Reporting order counts per customer for the last quarter, including customers who placed no orders
Finding imported order lines whose product id matches no product, with RightJoin and a null check

Frequently asked questions

Why can't I use ?. in the LeftJoin result selector?

On IQueryable the lambdas are expression trees, and C# does not allow the null-propagating operator in them. Write s == null ? 0 : s.OnHand. EF Core translates it, and it also works when the same expression runs in memory.

Do I still need GroupJoin and DefaultIfEmpty?

Not on .NET 10 with EF Core 10, where LeftJoin produces the same LEFT JOIN. Keep the old pattern only in code that must build against earlier versions; both translate to the same SQL.

Should I use LeftJoin when a navigation exists?

Usually not. Navigations in a Select or Include express the relationship from the model, and EF Core picks the join. LeftJoin is for relationships the model does not map, such as lookup or staging tables.

More examples

EF Core 10: customers with their order counts, including zero

Counting <code>OrderId != null</code> rather than rows is what makes inactive customers show 0 instead of 1: an unmatched customer still produces one joined row with null order columns. The whole query becomes one SQL statement with LEFT JOIN and GROUP BY.

using Microsoft.EntityFrameworkCore;

public record CustomerActivity(int CustomerId, string Name, int Orders, decimal Spent);

public sealed class ActivityReport(ShopDb db)
{
    public Task<List<CustomerActivity>> Since(DateTimeOffset from, CancellationToken ct) =>
        db.Customers
            .LeftJoin(
                db.Orders.Where(o => o.PlacedAt >= from),   // filter the inner side first
                c => c.Id,
                o => o.CustomerId,
                (c, o) => new { c.Id, c.Name, OrderId = o == null ? (int?)null : o.Id, Total = o == null ? 0m : o.Total })
            .GroupBy(x => new { x.Id, x.Name })
            .Select(g => new CustomerActivity(
                g.Key.Id,
                g.Key.Name,
                g.Count(x => x.OrderId != null),
                g.Sum(x => x.Total)))
            .OrderByDescending(r => r.Spent)
            .ToListAsync(ct);
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public decimal Total { get; set; }
    public DateTimeOffset PlacedAt { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
}

The SQL EF Core 10 generates

This is the shape of the statement, simplified for reading. The inner filter stays inside the joined subquery, which is exactly what keeps customers without recent orders in the result. Log the real SQL with <code>LogTo</code> or the <code>Microsoft.EntityFrameworkCore.Database.Command</code> category when tuning.

-- db.Customers.LeftJoin(db.Orders.Where(o => o.PlacedAt >= @from), ...) on PostgreSQL
SELECT c."Id", c."Name",
       count(o0."Id")::int        AS "Orders",
       COALESCE(sum(o0."Total"), 0.0) AS "Spent"
FROM "Customers" AS c
LEFT JOIN (
    SELECT o."Id", o."CustomerId", o."Total"
    FROM "Orders" AS o
    WHERE o."PlacedAt" >= @from
) AS o0 ON c."Id" = o0."CustomerId"
GROUP BY c."Id", c."Name"
ORDER BY "Spent" DESC;

RightJoin to find orphaned rows after an import

Testing the nullable side for <code>null</code> keeps only unmatched rows, so this is an anti-join; it is the one post-join filter on the optional side that is correct. <code>RightJoin</code> reads naturally when the side you must keep is the second argument; otherwise swap the arguments and use <code>LeftJoin</code>.

using Microsoft.EntityFrameworkCore;

public record OrphanLine(int LineId, int ProductId);

public sealed class ImportChecks(ShopDb db)
{
    // Every order line, with its product or null: the nulls point to products that do not exist.
    public Task<List<OrphanLine>> LinesWithoutProduct(CancellationToken ct) =>
        db.Products
            .RightJoin(
                db.ImportedLines,
                p => p.Id,
                l => l.ProductId,
                (p, l) => new { ProductId = p == null ? (int?)null : p.Id, LineId = l.Id, LineProductId = l.ProductId })
            .Where(x => x.ProductId == null)   // filtering the nullable side for null is the anti-join
            .Select(x => new OrphanLine(x.LineId, x.LineProductId))
            .ToListAsync(ct);
}

public class Product
{
    public int Id { get; set; }
}

public class ImportedLine
{
    public int Id { get; set; }
    public int ProductId { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<ImportedLine> ImportedLines => Set<ImportedLine>();
}

Discussion

Be the first to comment on this lesson.

Parameters

Return value

Errors

Changelog

See also

Parameters

Null handling depends on where the query runs

Choosing between join styles

Production failure modes

Example

When to use it

Frequently asked questions

Why can't I use ?. in the LeftJoin result selector?

Do I still need GroupJoin and DefaultIfEmpty?

Should I use LeftJoin when a navigation exists?

More examples

EF Core 10: customers with their order counts, including zero

The SQL EF Core 10 generates

RightJoin to find orphaned rows after an import

Related

Discussion