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?
answer
- inner join drops the unmatched
- one group per outer element
- null on the inner side
- DefaultIfEmpty before .NET 10
basics
~20 sJoin 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 linesusing 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
Know that Join only keeps matching pairs, so missing partners disappear, and that GroupJoin gives each outer element its list of matches.
Explain how GroupJoin and LeftJoin keep unmatched departments, the GroupJoin plus DefaultIfEmpty pattern before .NET 10, and why null keys never match.
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.
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.