Lesson 48 of 62Intermediate17 min

Joining

By the end of this lesson

Combine two sequences on a matching key.

Two sequences often hold halves of the same fact. Each Employee records a department name; each Department record holds that department's location and cost centre. To print an employee with their office you have to bring the two together.

Join does that. You give it the second sequence, a lambda that produces the key from an item on the left, a lambda that produces the key from an item on the right, and a lambda that builds the result from a matched pair. Four arguments, and the third and fourth are where the thinking is.

The behaviour to understand before you use it: only items that match on both sides come through. Everything else disappears, silently.

Employees with their office location

var withLocation = employees.Join(
    departments,
    employee => employee.Department,        // key from the left sequence
    department => department.Name,          // key from the right sequence
    (employee, department) => new          // what to build from a matched pair
    {
        employee.Name,
        Department = department.Name,
        department.Location,
    });

foreach (var row in withLocation)
{
    Console.WriteLine($"{row.Name} - {row.Department} - {row.Location}");
}
// Asha Mehta - Engineering - Pune
// Ravi Iyer - Engineering - Pune
// Neha Kulkarni - Engineering - Pune
// Imran Sheikh - Support - Nagpur
// Divya Rao - Support - Nagpur
// Tarun Bansal - Sales - Mumbai
// Meera Nair - Sales - Mumbai

Console.WriteLine(employees.Count);        // 8
Console.WriteLine(withLocation.Count());   // 7

The two key lambdas must produce the same type. Here both produce a string, so they can be compared. A mismatch is a compiler error, which is the one join mistake the compiler catches for you.
The result lambda receives both matched items, so you choose exactly which fields survive. Nothing forces you to keep whole objects — here the result has three fields out of a possible eight.
Eight employees went in and seven rows came out. Sanjay Gupta is in Finance, and there is no Department record named Finance, so he is gone. Legal has a Department record and nobody in it, so it never appears either.
Rows follow the order of the first sequence. Within a single employee's matches — if the right-hand side had two records for one department — the order follows the right-hand sequence.
This kind of join, which keeps only matched pairs, is called an inner join. The name comes from databases, and the behaviour is the same there.

Common mistake

An inner join loses rows and does not tell you

Seven rows from eight employees is a data quality report, and the query printed nothing about it. Sanjay Gupta vanished from a payroll list because a reference record was missing. If that list is what finance pays from, the bug is expensive and invisible.

When two sequences should match one-to-one, compare the counts and react when they differ. A single assertion or log line — joined 7 of 8 employees — turns a silent loss into something somebody notices.

Keys that differ by case or stray spaces do not match. "sales" is not "Sales", and "Sales " is not "Sales". Join uses the default equality comparison for the key type, so pass a comparer when you mean something looser: Join(departments, e => e.Department, d => d.Name, ..., StringComparer.OrdinalIgnoreCase).

Joining on a display name is fragile for the same reason. When both sides have an id, join on the id — names get corrected, retyped and translated.

Join only handles equality between keys. There is no overload for "key is within this range" or "dates overlap". Those need a different approach, usually a Where over a pair of sequences or a query the database can express.

Keeping the unmatched items: GroupJoin

Every department, including the empty one

var rosters = departments.GroupJoin(
    employees,
    department => department.Name,
    employee => employee.Department,
    (department, members) => new
    {
        department.Name,
        department.Location,
        Headcount = members.Count(),
        Names = string.Join(", ", members.Select(e => e.Name)),
    });

foreach (var roster in rosters)
{
    Console.WriteLine($"{roster.Name} ({roster.Location}): {roster.Headcount} - {roster.Names}");
}
// Engineering (Pune): 3 - Asha Mehta, Ravi Iyer, Neha Kulkarni
// Support (Nagpur): 2 - Imran Sheikh, Divya Rao
// Sales (Mumbai): 2 - Tarun Bansal, Meera Nair
// Legal (Mumbai): 0 -

The first three arguments are the same idea as Join. The difference is the fourth: the result lambda receives one department and a sequence of all its matching employees, rather than one pair at a time.
Legal appears with a headcount of zero and an empty name list. That is the point of GroupJoin — an outer item with no matches still produces a row, holding an empty sequence.
Sanjay Gupta is still missing. Departments are the outer sequence here, so unmatched departments survive and unmatched employees do not. Which side is outer decides whose gaps are preserved.
string.Join is not LINQ. It joins strings with a separator, and the name collision with the LINQ Join operator is unfortunate and worth being clear about.
The result is a parent-with-children shape, which is what most screens want: a department heading with its people underneath.

The two operators answer different questions:

	Join	GroupJoin
Result shape	One row per matching pair	One row per outer item, each carrying its matches
Outer items with no match	Dropped	Kept, with an empty sequence of matches
With this data	7 rows from 8 employees	4 rows from 4 departments, one of them empty
Closest database equivalent	INNER JOIN	LEFT JOIN, with the right-hand rows gathered per left-hand row
Reach for it when	You want a flat table of related facts	You want parents with their children, including childless parents

When to use this

When not to write a join at all

When one side is small and you look things up repeatedly, a dictionary is clearer than a join. departments.ToDictionary(d => d.Name) once, then a TryGetValue per employee, reads better than a join buried in a larger query — and it makes the missing-key case explicit rather than silent.

When you are working with Entity Framework Core, the .NET library that maps C# types to database tables, related data usually reaches you through a navigation property: a property on Employee that returns its Department object, with the relationship configured once. Writing Join by hand then duplicates something the model already knows.

When the two sequences are not really related by a key. Zip pairs two sequences by position, and Concat runs one after the other. Using a join for either produces confusing code.

One cost worth knowing, because it decides argument order for large inputs: Join reads the whole inner sequence first and builds a lookup from it, then streams the outer sequence through that lookup. Putting the smaller sequence second keeps less in memory.

Summary

Join matches two sequences on equal keys and builds a result from each matched pair
An inner join drops items with no match on either side, and reports nothing about it
GroupJoin keeps every outer item and gives each one its own sequence of matches, possibly empty
GroupJoin plus DefaultIfEmpty plus SelectMany is how a left outer join is expressed
Case, whitespace and unstable display names are the usual reasons a join finds fewer matches than expected

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Try it yourself

Produce one line per employee showing their name and office location, including Sanjay Gupta. Where the department has no record, show "Unknown".

All eight employees must appear. Start from GroupJoin.

Show solution

Join cannot do this, because dropping unmatched items is what an inner join is. The pattern is GroupJoin to get each employee with their matches, DefaultIfEmpty to turn an empty match set into a single null, then SelectMany to flatten back to one row per employee. This combination is how a left outer join is written in LINQ, and it is worth recognising rather than deriving each time.

The Department? d in the final lambda is nullable on purpose: DefaultIfEmpty supplies null when there was no match, so d?.Location ?? "Unknown" is the line that handles the gap. With nullable reference types switched on, the compiler points at exactly this spot if you forget.

A simpler alternative for a small right-hand side: build a dictionary from departments and use TryGetValue per employee. Fewer moving parts, the same result, and the missing-key branch is impossible to overlook. Prefer it unless you specifically need the query to compose with more operators.

var allEmployees = employees
    .GroupJoin(
        departments,
        employee => employee.Department,
        department => department.Name,
        (employee, matches) => new { Employee = employee, Matches = matches })
    .SelectMany(
        pair => pair.Matches.DefaultIfEmpty(),
        (pair, department) => new
        {
            pair.Employee.Name,
            Location = department?.Location ?? "Unknown",
        });

foreach (var row in allEmployees)
{
    Console.WriteLine($"{row.Name} - {row.Location}");
}
// Asha Mehta - Pune
// Ravi Iyer - Pune
// Neha Kulkarni - Pune
// Imran Sheikh - Nagpur
// Divya Rao - Nagpur
// Tarun Bansal - Mumbai
// Meera Nair - Mumbai
// Sanjay Gupta - Unknown

Think about it

The Join version omits Legal. The GroupJoin version shows Legal with a headcount of zero.

Which one is correct? Answer for two different reports: a payroll run, and a staffing review that looks for gaps.

Show solution

For a payroll run, the missing Legal row is correct. There is nobody to pay, so a row for Legal would be noise. Sanjay Gupta's disappearance, on the other hand, is a serious defect in the same report — the inner join removed a person who must be paid.

For a staffing review, Legal with zero is the most valuable row on the page. A department with no people is exactly what the report exists to surface, and an inner join hides it.

So the choice of operator is a decision about what "no match" means for the question being asked, and it belongs with whoever understands the question. A join is not a mechanical step; it encodes a rule about absent data, and the failure mode is that absent data looks like no data at all.

Saved in this browser only.