Join allows you to correlate two sequences based on matching keys. It's the most common way to rebuild related data in your application.
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
);
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.
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)."