LeftJoin and RightJoin (EF Core 10)

Write outer joins with the LeftJoin and RightJoin operators instead of the GroupJoin, SelectMany and DefaultIfEmpty pattern, and handle nulls.

Syntax

db.Customers.LeftJoin(db.Orders,
    c => c.Id, o => o.CustomerId,
    (c, o) => new { c.Name, Total = (decimal?)o.Total })

LINQ had inner joins (Join) from the start but no outer join operator. A left join had to be spelled as a pattern: GroupJoin to pair each outer element with its group of matches, then SelectMany over group.DefaultIfEmpty() so an empty group yields one null. EF recognised that exact shape and emitted LEFT JOIN. It worked, but it was hard to read, easy to get subtly wrong, and impossible to write in query syntax without join ... into ... from ... DefaultIfEmpty().

The operators

EF Core 10 translates LeftJoin and RightJoin, and in .NET 10 they are part of LINQ itself on both Enumerable and Queryable, so the same call works on in-memory lists. The signature mirrors Join: outer source, inner source, outer key selector, inner key selector, result selector. In the result selector the unmatched side is null: (customer, order) => ... with order null for a customer who has no orders.

Write	SQL
`customers.Join(orders, ...)`	`INNER JOIN`
`customers.LeftJoin(orders, ...)`	`LEFT JOIN`: every customer, order columns null when unmatched
`orders.RightJoin(customers, ...)`	`RIGHT JOIN`: the same rows with the sources swapped
`GroupJoin` + `SelectMany` + `DefaultIfEmpty`	`LEFT JOIN`, the older equivalent

There is no query-syntax keyword for either; the method form is the only form. RightJoin exists for symmetry; swapping the two sources of a LeftJoin gives the same rows and most teams find the left form easier to read, so prefer it.

Nulls are the whole point

Projecting a non-nullable member from the nullable side, (c, o) => new { c.Name, o.Total }, compiles (with a nullable warning), and the SQL runs, but materialising a customer without orders throws InvalidOperationException: Nullable object must have a value, because Total is a decimal and the column came back NULL. Cast to the nullable type ((decimal?)o.Total) or test o == null in the projection. In memory the rule differs slightly: for a reference type the inner element is null, for a value type such as a tuple it is default.

When to use it

If a navigation exists, use it: c.Orders in a projection or an optional reference such as o.ApprovedBy already produces the right join. LeftJoin is for joins the model does not describe: matching payments to invoices by a reference number, joining to a keyless reporting view, or relating tables from different bounded contexts that deliberately have no foreign key. Composite keys work with anonymous types whose member names and types match on both sides, which becomes ON a = b AND c = d. Combine it with GroupBy to count matches including zeros, the classic report that an inner join gets wrong by dropping customers with nothing to count.

Example

Loading editor…

Press Run to execute the code.

When to use it

List invoices due before month end with the sum of matching bank payments, including invoices that have no payment yet
Count orders per customer for a sales report without dropping customers who have placed no orders
Find orders whose customer row is missing after a botched data import with a RightJoin from customers to orders

More examples

Matching payments to invoices in EF Core

The whole pipeline is one SQL statement: a <code>LEFT JOIN</code> on <code>Number = Reference</code>, a <code>GROUP BY</code>, and a <code>HAVING</code>-style filter. Casting <code>payment.Amount</code> to <code>decimal?</code> is what lets invoices with no payment at all come back with <code>Paid = 0</code> instead of throwing. An inner <code>Join</code> here would drop exactly the invoices the report exists to find.

using Microsoft.EntityFrameworkCore;

public static class Reconciliation
{
    // Payments arrive from the bank with a free-text reference, so there is no foreign key
    // and no navigation: an explicit LeftJoin on the invoice number is the natural query.
    public static Task<List<InvoiceStatus>> UnpaidOrPartlyPaidAsync(BillingDb db, DateOnly dueBefore, CancellationToken ct) =>
        db.Invoices
            .Where(i => i.DueOn < dueBefore)
            .LeftJoin(db.BankPayments,
                invoice => invoice.Number,
                payment => payment.Reference,
                (invoice, payment) => new { invoice.Number, invoice.Amount, Paid = (decimal?)payment.Amount })
            .GroupBy(x => new { x.Number, x.Amount })
            .Select(g => new { g.Key.Number, g.Key.Amount, Paid = g.Sum(x => x.Paid) ?? 0m })
            .Where(s => s.Paid < s.Amount)   // filter on the anonymous type, before the record is built
            .OrderBy(s => s.Number)
            .Select(s => new InvoiceStatus(s.Number, s.Amount, s.Paid))
            .ToListAsync(ct);
}

public sealed record InvoiceStatus(string Number, decimal Amount, decimal Paid);

public sealed class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();
    public DbSet<BankPayment> BankPayments => Set<BankPayment>();
}

public sealed class Invoice
{
    public int Id { get; set; }
    public required string Number { get; set; }   // INV-2026-00042
    public decimal Amount { get; set; }
    public DateOnly DueOn { get; set; }
}

public sealed class BankPayment
{
    public int Id { get; set; }
    public required string Reference { get; set; }
    public decimal Amount { get; set; }
}

The SQL EF Core generates

Output from the Npgsql provider. The old pattern and the new operator are interchangeable at the SQL level, so switching is a readability change with no plan change. For composite keys, the anonymous types must use the same member names in the same order, which is why the inner key is written <code>Id = o.CustomerId</code>; EF combines the pairs with <code>AND</code>.

-- customers.LeftJoin(orders, c => c.Id, o => o.CustomerId, (c, o) => new { c.Name, Total = (decimal?)o.Total })
SELECT c."Name", o."Total"
FROM "Customers" AS c
LEFT JOIN "Orders" AS o ON c."Id" = o."CustomerId";

-- The GroupJoin + SelectMany + DefaultIfEmpty form produces exactly the same statement.

-- orders.RightJoin(customers, o => o.CustomerId, c => c.Id, (o, c) => new { c.Name, Total = (decimal?)o.Total })
SELECT c."Name", o."Total"
FROM "Orders" AS o
RIGHT JOIN "Customers" AS c ON o."CustomerId" = c."Id";

-- Composite key: new { c.Id, c.Region } and new { Id = o.CustomerId, o.Region }
SELECT c."Name", o."Id", o."CustomerId", o."Region", o."Total"
FROM "Customers" AS c
LEFT JOIN "Orders" AS o ON c."Id" = o."CustomerId" AND c."Region" = o."Region";

-- Count including zeros: LeftJoin then GroupBy(x => new { x.Id, x.Name }) with g.Count(x => x.o != null)
SELECT c."Name", count(*) FILTER (WHERE o."Id" IS NOT NULL)::int AS "Orders"
FROM "Customers" AS c
LEFT JOIN "Orders" AS o ON c."Id" = o."CustomerId"
GROUP BY c."Id", c."Name";

The null pitfall and two fixes

The broken version passes every test whose seed data gives each customer an order, then fails in production on the first new customer. The <code>o!</code> only silences the compiler; it changes nothing at run time. Prefer nullable members when the API should say there is no order, and a fallback only when a zero is genuinely the right business answer.

using Microsoft.EntityFrameworkCore;

public static class CustomerReports
{
    // Broken: o.Total is decimal. For a customer with no orders the column is NULL and
    // materialisation throws InvalidOperationException: Nullable object must have a value.
    public static Task<List<CustomerLastOrder>> Broken(ShopDb db, CancellationToken ct) =>
        db.Customers
            .LeftJoin(db.Orders, c => c.Id, o => o.CustomerId,
                (c, o) => new CustomerLastOrder(c.Name, o!.Total, o.PlacedAt))
            .ToListAsync(ct);

    // Fix 1: make the projected members nullable.
    public static Task<List<CustomerLastOrderOrNone>> WithNullableMembers(ShopDb db, CancellationToken ct) =>
        db.Customers
            .LeftJoin(db.Orders, c => c.Id, o => o.CustomerId,
                (c, o) => new CustomerLastOrderOrNone(c.Name, (decimal?)o!.Total, (DateTime?)o.PlacedAt))
            .ToListAsync(ct);

    // Fix 2: decide the fallback in the projection; EF translates the conditional to
    // CASE WHEN o."Id" IS NULL THEN 0.0 ELSE o."Total" END.
    public static Task<List<CustomerOrderValue>> WithFallback(ShopDb db, CancellationToken ct) =>
        db.Customers
            .LeftJoin(db.Orders, c => c.Id, o => o.CustomerId,
                (c, o) => new CustomerOrderValue(c.Name, o == null ? 0m : o.Total))
            .ToListAsync(ct);
}

public sealed record CustomerLastOrder(string Name, decimal Total, DateTime PlacedAt);
public sealed record CustomerLastOrderOrNone(string Name, decimal? Total, DateTime? PlacedAt);
public sealed record CustomerOrderValue(string Name, decimal Total);

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

public sealed class Customer
{
    public int Id { get; set; }
    public required string Name { get; set; }
}

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

Discussion

Be the first to comment on this lesson.

The operators

Nulls are the whole point

When to use it

Example

When to use it

More examples

Matching payments to invoices in EF Core

The SQL EF Core generates

The null pitfall and two fixes

Related

Discussion