skip to content

In C# query syntax, how do join … on … equals and join … into translate, and how do you write a left outer join?

level: middleimportance: should knowfreq 45%

answer

  1. equality only
  2. outer key on the left
  3. into changes the method
  4. DefaultIfEmpty fills the gap
  5. composite keys need matching names

basics

~20 s

join … on a equals b compiles to Join; adding into compiles to GroupJoin. Only equality is allowed, the outer source's key goes left of equals, and a left outer join is join … into g followed by from x in g.DefaultIfEmpty().

solid answer

~40 s

`join b in books on a.Id equals b.AuthorId` becomes `authors.Join(books, a => a.Id, b => b.AuthorId, …)`. The contextual keyword `equals`, not `==`, marks an equijoin: the left key may use only the earlier range variables and the right key only the new one, so swapping them is CS1937 and CS1938, and writing `==` is CS0744. A composite key is an anonymous object, and both sides need the same property names and types or inference fails with CS1941. Appending `into shelf` turns the call into `GroupJoin`: every outer element survives, paired with a possibly empty `shelf`. Following it with `from b in shelf.DefaultIfEmpty()` flattens that into a left outer join (`GroupJoin` plus `SelectMany`). C# has no keyword for an outer join; .NET 10's `Enumerable.LeftJoin` is the method-syntax alternative.

code

csharp · 27 lines
csharp
using System.Linq;

Author[] authors = [new(1, "Achebe"), new(2, "Le Guin"), new(3, "Baldwin")];
Book[] books = [new("Things Fall Apart", 1), new("The Dispossessed", 2), new("Earthsea", 2)];

// join ... on outerKey equals innerKey  ->  Enumerable.Join (inner join)
var pairs = from a in authors
            join b in books on a.Id equals b.AuthorId
            select $"{a.Name}: {b.Title}";

// join ... into shelf  ->  Enumerable.GroupJoin (every author, with a possibly empty shelf)
var shelves = from a in authors
              join b in books on a.Id equals b.AuthorId into shelf
              select $"{a.Name} ({shelf.Count()})";

// join ... into + from ... DefaultIfEmpty()  ->  GroupJoin + SelectMany: a left outer join
var leftOuter = from a in authors
                join b in books on a.Id equals b.AuthorId into shelf
                from b in shelf.DefaultIfEmpty()
                select $"{a.Name}: {b?.Title ?? "(no books)"}";

Console.WriteLine(string.Join(" | ", pairs));      // Achebe: Things Fall Apart | Le Guin: The Dispossessed | Le Guin: Earthsea
Console.WriteLine(string.Join(" | ", shelves));    // Achebe (1) | Le Guin (2) | Baldwin (0)
Console.WriteLine(string.Join(" | ", leftOuter));  // ... | Baldwin: (no books)

record Author(int Id, string Name);
record Book(string Title, int AuthorId);

go deeper

for a junior

Recall the shape join x in xs on outerKey equals innerKey, and that it keeps only matching pairs.

for a middle

Explain Join versus GroupJoin as the compiler emits them, the equals ordering rule behind CS1937 and CS1938, and the into plus DefaultIfEmpty pattern for a left outer join.

for a senior

Read a failing join quickly: swapped keys, mismatched composite-key names (CS1941), a null key that never matches, or a left join that forgot to handle the default element.

for a principal

On .NET 10, weigh the query-syntax left-join pattern against LeftJoin in method syntax as a readability rule for the codebase rather than a performance question.

## Inner join: join … on … equals A `join` clause correlates the current range variable with a second sequence by key: ```csharp from a in authors join b in books on a.Id equals b.AuthorId select $"{a.Name}: {b.Title}" ``` The compiler translates it into `Enumerable.Join(authors, books, a => a.Id, b => b.AuthorId, (a, b) => …)`. The first lambda is the **outer key selector**, the second the **inner key selector**, and when `select` follows directly it becomes the **result selector**. Only pairs whose keys are equal appear, so an author with no books disappears from the output: this is an **inner equijoin**. In LINQ to Objects a key that is `null` on either side never matches anything, because `Join` leaves null inner keys out of its lookup. When more clauses follow the `join`, the result selector instead packs both range variables into an anonymous object, `(a, b) => new { a, b }`, so that `where`, `orderby` and `select` after the join can still name `a` and `b`; a second `join` after the first chains another `Join` call on that combined sequence, as in the catalogue example that joins loans to books and books to authors. ## What equals enforces `equals` is a contextual keyword used only in `join`, and the compiler holds it to three rules: - **Equality only.** There is no `join … on a.Year > b.Year`. Writing `==` instead of `equals` is a syntax error, CS0744: "Expected contextual keyword 'equals'". - **Order matters.** The left expression may reference only range variables declared before the `join`; the right only the new one. Reversing them gives CS1937, "The name 'b' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'." and CS1938 for the other side. - **One key type.** Both sides must infer the same key type. A composite key is written as an anonymous object on each side, `on new { a.Id, a.Name } equals new { Id = b.AuthorId, Name = b.Author }`, and the property names, types and order must match; if they do not, the compiler reports CS1941: "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'." The keyword offers no slot for a custom `IEqualityComparer<TKey>`; that needs the `Join` overload that takes one, in method syntax. ## join … into: a group join Adding `into shelf` changes the translation to `Enumerable.GroupJoin`. Instead of one output per matching pair, there is one output per outer element, and `shelf` is the `IEnumerable<Book>` of its matches, empty when there are none. After `into`, the inner range variable `b` is no longer in scope; only `a` and `shelf` are. | Query form | Method | One result per | Unmatched outer elements | |---|---|---|---| | `join … on … equals …` | `Join` | matching pair | dropped | | `join … into g` | `GroupJoin` | outer element | kept, `g` empty | | `join … into g` + `from x in g.DefaultIfEmpty()` | `GroupJoin` + `SelectMany` | pair, or outer plus default | kept, `x` is `default` | ## Left outer join in query syntax C# has no `left` keyword, so a left outer join is assembled from two clauses: 1. `join b in books on a.Id equals b.AuthorId into shelf` keeps every author with its (maybe empty) shelf. 2. `from b in shelf.DefaultIfEmpty()` flattens each shelf; `DefaultIfEmpty` turns an empty shelf into a one-element sequence holding `default(Book)`, which is `null` for a class or record class. 3. The `select` must handle that null, for example `b?.Title ?? "(no books)"`. The compiler emits `GroupJoin` followed by `SelectMany`, with a transparent identifier carrying `a` and `shelf` between them. .NET 10 added `Enumerable.LeftJoin` and `RightJoin`, which do the same in one method call; they have no query keyword, so a query-syntax left join still uses the pattern above. ## Conditions that are not equality For a range join, a "not equal" test or any other predicate, drop `join` and use a second `from` with `where`: `from a in authors from b in books where b.Year > a.Born select …`. That compiles to `SelectMany` over every pair followed by `Where`, so it tests each pair, while `join` builds one lookup of the inner keys and probes it for each outer element.

  • Why does C# use the equals keyword in a join clause instead of the == operator?
    Because the clause is not a Boolean expression: it supplies two separate key selectors, one evaluated over the outer range variables and one over the inner, which become the two lambdas passed to `Join` or `GroupJoin`. `equals` makes the split explicit and limits the join to equality. Writing `==` is CS0744, and referencing the wrong side's variable is CS1937 or CS1938.
  • How do you join on two properties at once in C# query syntax?
    Use an anonymous object on each side of `equals`, for example `on new { a.Id, a.Name } equals new { Id = b.AuthorId, Name = b.Author }`. Anonymous types compare by property value, so the keys match when every property matches. Names, types and order must line up on both sides, which is why the second object renames its properties; otherwise inference fails with CS1941.

  • join … on a.Id == b.AuthorId compiles like a where clause.
  • The two sides of equals can be written in either order.
  • join … into still drops outer elements that have no match.
  • C# query syntax has a left join keyword for outer joins.
  • Any condition, such as greater than, can follow on in a join clause.

Assumes .NET 10, which added Enumerable.LeftJoin and RightJoin; the join … into plus DefaultIfEmpty pattern works on every supported release.