Joining on Multiple Columns with Entity Framework and LINQ

Learning LINQ

Introduction to LINQ

LINQ: group query results – an Introduction

LINQ queries and C# Methods

LINQ Data Transformations and Projections

Joining on Multiple Columns with Entity Framework and LINQ

In SQL, joining on two columns is barely worth a thought:

SELECT i.InvoiceNumber, p.PaidOn
FROM Invoices i
JOIN Payments p
  ON i.TenantId = p.TenantId
 AND i.InvoiceNumber = p.InvoiceNumber

LINQ has no AND in a join clause. A join compares exactly one key on the left with exactly one key on the right, and that trips people up the first time they hit a composite key or a multi-tenant table.

The trick is that “one key” doesn’t have to mean “one column”.

Use an anonymous type as the key

Wrap both sides in an anonymous type and let structural equality do the work:

var query =
    from invoice in db.Invoices
    join payment in db.Payments
        on new { invoice.TenantId, invoice.InvoiceNumber }
        equals new { payment.TenantId, payment.InvoiceNumber }
    select new
    {
        invoice.InvoiceNumber,
        invoice.Total,
        payment.PaidOn
    };

Method syntax, if you prefer it:

var query = db.Invoices
    .Join(
        db.Payments,
        invoice => new { invoice.TenantId, invoice.InvoiceNumber },
        payment => new { payment.TenantId, payment.InvoiceNumber },
        (invoice, payment) => new
        {
            invoice.InvoiceNumber,
            invoice.Total,
            payment.PaidOn
        });

Both produce the SQL you expected — a single INNER JOIN with two conditions. No subqueries, no client evaluation.

Three rules, and all of them bite

Anonymous types are compared by shape, and the compiler treats two shapes as the same type only when everything lines up.

Property names must match. new { invoice.InvoiceNumber } and new { payment.ExternalRef } are different types, so the join won’t compile. Name them explicitly:

on new { invoice.TenantId, Number = invoice.InvoiceNumber }
equals new { payment.TenantId, Number = payment.ExternalRef }

Types must match exactly. int and int? are not the same thing, and this is the single most common cause of the type-inference error on a join clause. Cast the non-nullable side:

on new { invoice.TenantId, Number = (int?)invoice.InvoiceNumber }
equals new { payment.TenantId, Number = payment.InvoiceNumber }

Order matters. new { A, B } and new { B, A } are two different anonymous types, even though they carry the same data. Keep the properties in the same sequence on both sides.

One warning about that nullable cast: it makes the code compile, but it doesn’t change what the database does. EF translates the join to = comparisons, and in SQL, NULL = NULL is not true. Rows where either key column is null simply won’t match — which is not how the same anonymous types would behave in LINQ to Objects. If a join key is nullable, look at the generated SQL before you trust the result.

The alternative that reads better

You can skip join entirely and let the where clause carry the conditions:

var query =
    from invoice in db.Invoices
    from payment in db.Payments
    where invoice.TenantId == payment.TenantId
       && invoice.InvoiceNumber == payment.InvoiceNumber
    select new { invoice.InvoiceNumber, payment.PaidOn };

EF Core turns this into the same inner join. It costs you nothing, it sidesteps all three rules above, and it scales more gracefully when a third condition shows up. I reach for it whenever the join keys aren’t a clean pair.

Left joins on multiple columns

Before .NET 10, an outer join meant the group-join dance:

var query =
    from invoice in db.Invoices
    join payment in db.Payments
        on new { invoice.TenantId, invoice.InvoiceNumber }
        equals new { payment.TenantId, payment.InvoiceNumber }
        into payments
    from payment in payments.DefaultIfEmpty()
    select new
    {
        invoice.InvoiceNumber,
        PaidOn = (DateTime?)payment.PaidOn
    };

.NET 10 and EF Core 10 added first-class LeftJoin and RightJoin operators, and composite keys work there exactly the same way:

var query = db.Invoices
    .LeftJoin(
        db.Payments,
        invoice => new { invoice.TenantId, invoice.InvoiceNumber },
        payment => new { payment.TenantId, payment.InvoiceNumber },
        (invoice, payment) => new
        {
            invoice.InvoiceNumber,
            PaidOn = payment != null ? payment.PaidOn : (DateTime?)null
        });

They’re method-syntax only — there’s no new query-expression keyword — but they translate to a plain LEFT JOIN and they’re a lot easier to read six months later.

If you own the model, don’t write the join at all

An explicit join often signals a missing relationship in the model. EF Core supports composite foreign keys:

modelBuilder.Entity<Payment>()
    .HasOne(p => p.Invoice)
    .WithMany(i => i.Payments)
    .HasForeignKey(p => new { p.TenantId, p.InvoiceNumber })
    .HasPrincipalKey(i => new { i.TenantId, i.InvoiceNumber });

Once that’s configured, payment.Invoice.Total generates the same SQL and you never have to think about property names, nullability, or ordering again. Hand-written joins are for the cases where you genuinely can’t map the relationship — views, keyless entities, tables you don’t control.

Written with AI assistance.