skip to content

In C# LINQ to Objects, how do you list departments with no staff, and what do Join, GroupJoin and .NET 10's LeftJoin each return?

level: middleimportance: should knowfreq 40%

answer

  1. inner join drops the unmatched
  2. one group per outer element
  3. null on the inner side
  4. DefaultIfEmpty before .NET 10

basics

~20 s

Join is an inner join, so departments without staff vanish. GroupJoin yields every department with a possibly empty sequence of matches, so filter for empty groups. LeftJoin, added in .NET 10, yields each department with a null employee when nothing matches.

solid answer

~40 s

`Join` is an inner equijoin: it yields one result per matching department-employee pair, so a department with no staff produces nothing. `GroupJoin` yields exactly one result per department, passing the department and an `IEnumerable<Employee>` of its matches, which is empty for an unstaffed department, so `GroupJoin(...).Where(x => !x.team.Any())` lists them. .NET 10 added `LeftJoin`: one row per match plus one row with `null` (`default`) in the inner slot for a department with none, its result selector typed `Func<TOuter, TInner?, TResult>`. Before .NET 10 the same left outer join was `GroupJoin` followed by `SelectMany(x => x.team.DefaultIfEmpty(), ...)`. All three build a lookup of the inner sequence on first enumeration and keep the outer sequence's order, and a `null` key never matches anything.

code

csharp · 25 lines
csharp
using System;
using System.Linq;

Department[] departments = [new(1, "Payroll"), new(2, "Legal"), new(3, "Archive")];
Employee[] staff = [new("Ana", 1), new("Ben", 1), new("Cleo", 2)];

var inner = departments.Join(staff, d => d.Id, e => e.DeptId,
    (d, e) => $"{d.Name}/{e.Name}");
Console.WriteLine(string.Join(", ", inner));      // Archive is missing

var grouped = departments.GroupJoin(staff, d => d.Id, e => e.DeptId,
    (d, team) => $"{d.Name}: {team.Count()}");
Console.WriteLine(string.Join(", ", grouped));    // Archive: 0

var left = departments.LeftJoin(staff, d => d.Id, e => e.DeptId,   // .NET 10
    (d, e) => $"{d.Name}/{e?.Name ?? "(none)"}");
Console.WriteLine(string.Join(", ", left));

var empty = departments.GroupJoin(staff, d => d.Id, e => e.DeptId, (d, team) => (d, team))
    .Where(x => !x.team.Any())
    .Select(x => x.d.Name);
Console.WriteLine(string.Join(", ", empty));      // Archive

record Department(int Id, string Name);
record Employee(string Name, int DeptId);

go deeper

for a junior

Know that Join only keeps matching pairs, so missing partners disappear, and that GroupJoin gives each outer element its list of matches.

for a middle

Explain how GroupJoin and LeftJoin keep unmatched departments, the GroupJoin plus DefaultIfEmpty pattern before .NET 10, and why null keys never match.

for a senior

Choose the join by the report's question about absence, read legacy DefaultIfEmpty code confidently, and handle the null inner value LeftJoin passes to the result selector.

for a principal

Plan when to retire GroupJoin-plus-DefaultIfEmpty idioms in favour of LeftJoin as projects move to .NET 10, without churning code that still multi-targets.

## The question behind the operators An HR system has a `Department` list and an `Employee` list linked by `DeptId`. "Departments with no staff" is a question about rows that have **no partner**, and that is exactly where the LINQ join operators differ. All of them are **equijoins**: they match an outer key and an inner key with `EqualityComparer<TKey>.Default` or a comparer you pass. | Operator | Results per department | A department with no staff | |---|---|---| | `Join` | one per matching employee | yields nothing | | `GroupJoin` | exactly one, with a sequence of employees | yields one result with an empty sequence | | `LeftJoin` (.NET 10) | one per matching employee, at least one | yields one result with `null` for the employee | | `RightJoin` (.NET 10) | keeps every employee instead | not the tool for this question | ## Join: an inner join `departments.Join(staff, d => d.Id, e => e.DeptId, (d, e) => ...)` calls the result selector once for every pair whose keys are equal. Archive has no staff, so it never appears. `Join` is the right operator for "each employee with their department name" and the wrong one for any question about absence. ## GroupJoin: one result per outer element `GroupJoin` passes each department together with **all** its matching employees as an `IEnumerable<Employee>`: ```csharp var unstaffed = departments .GroupJoin(staff, d => d.Id, e => e.DeptId, (d, team) => (d, team)) .Where(x => !x.team.Any()) .Select(x => x.d.Name); // Archive ``` An unstaffed department still produces a result; its sequence is simply empty. In query syntax this is `join ... into team`. ## LeftJoin in .NET 10 .NET 10 added `Enumerable.LeftJoin` and `Enumerable.RightJoin`. `LeftJoin` yields a row for every match and, for an outer element with no match, one row whose inner value is `default(TInner)`: `null` for a class or record. Its result selector is declared `Func<TOuter, TInner?, TResult>`, so nullable analysis reminds you to handle the missing employee: ```csharp var rows = departments.LeftJoin(staff, d => d.Id, e => e.DeptId, (d, e) => $"{d.Name}/{e?.Name ?? "(none)"}"); // Payroll/Ana, Payroll/Ben, Legal/Cleo, Archive/(none) ``` Neither method has a query-syntax keyword. Compiled against .NET 9 the call fails with CS1061 because the method does not exist there. ## The pattern before .NET 10 Older code builds the same left outer join from two operators: 1. `GroupJoin` pairs each department with its team; 2. `SelectMany(x => x.team.DefaultIfEmpty(), (x, e) => ...)` flattens it, and `DefaultIfEmpty` turns an empty team into a sequence holding one `null`, so the department survives the flattening. Recognising this shape matters when reading existing code, and replacing it with `LeftJoin` is a readability change once the project targets .NET 10. How a database provider translates any of these into SQL is a separate subject, covered with EF Core. ## Behaviour shared by all of them - **Deferred**: nothing runs until enumeration. - **Inner side buffered, outer side streamed**: on first enumeration the inner sequence is read once into a lookup keyed by `innerKeySelector`; the outer sequence is then walked in order, so results follow the departments' order, with each department's matches in the employees' order. - **Null keys never match**: the lookup skips inner elements whose key is `null`, so an employee with a `null` department id joins with nothing, and a department with a `null` key finds no partner (with `LeftJoin` it still yields its row with `null`). - **Equality matters**: a key that is a class without value equality matches only the same instance; records and tuples give value equality. ## Picking one - "Each employee with their department": `Join`. - "Each department with its staff list" or "departments with no staff": `GroupJoin`. - "A flat report with one line per department even when empty": `LeftJoin` on .NET 10, `GroupJoin` plus `DefaultIfEmpty` before it.

  • How was a left outer join written in LINQ before .NET 10?
    With `GroupJoin` followed by `SelectMany` over `DefaultIfEmpty()`: `departments.GroupJoin(staff, d => d.Id, e => e.DeptId, (d, team) => (d, team)).SelectMany(x => x.team.DefaultIfEmpty(), (x, e) => ...)`. `DefaultIfEmpty` replaces an empty team with a single `null`, so the department survives the flattening. In query syntax it is `join ... into team` then `from e in team.DefaultIfEmpty()`.
  • An employee record has a null DeptId; what does Join or LeftJoin do with it?
    Nothing matches it. When the operators build the lookup of the inner sequence, they skip elements whose key is `null`, so a `null` key on the inner side joins with no outer element, even another `null`. On the outer side, a department with a `null` key finds no match: `Join` drops it, `LeftJoin` yields it once with `null` as the employee.

  • Join keeps departments that have no matching employees
  • GroupJoin returns one row per matching pair like Join
  • LeftJoin has been in LINQ since .NET Framework
  • A null key on both sides makes Join match them
  • LeftJoin can be written with a join keyword in query syntax

Assumes .NET 10, which added Enumerable.LeftJoin and RightJoin; on .NET 9 and earlier a left outer join needs GroupJoin with DefaultIfEmpty.