Merging Data Streams

Join allows you to correlate two sequences based on matching keys. It's the most common way to rebuild related data in your application.

1. How Join Works

A Join takes an outer sequence, an inner sequence, and key selectors for both. It then yields a combined result for every match found. **Performance Note:** LINQ Join internally uses a Hash Join algorithm, making it extremely fast (O(N+M)) compared to nested loops.

var query = students.Join(
    grades,
    student => student.Id,   // Outer key
    grade => grade.StudentId, // Inner key
    (student, grade) => new { student.Name, grade.Score } // Result
);
        

2. Join vs Where/SelectMany

In many cases, you can achieve the same result using SelectMany or a Where clause. However, Join is more performant because it uses a hash table for the lookup instead of a full scan for every outer element.

3. Architect Insight

Q: "Should I join in C# or in the Database?"

Architect Answer: "Join in the **Database** (IQueryable) whenever possible. This allows the DB engine to optimize the join using its indexes and only send the final combined result over the network. Only join in C# if you are merging data from two completely different sources (e.g., a SQL table and a JSON API response)."