Join Clause

The join clause in C# is a Language Integrated Query (LINQ) operator used to correlate elements from two distinct data sequences based on matching keys. It performs an equijoin, yielding a flattened output sequence containing elements from both the outer and inner data sources where the specified key properties evaluate to true for equality.

Core Mechanics

The join operation requires two sequences: an outer sequence (the initial data source) and an inner sequence (the data source being joined).

The correlation is defined using the on and equals contextual keywords. C# strictly enforces the use of equals for joins; standard relational operators (such as ==, >, or <) are not permitted. Furthermore, the keys being compared must be of the same type or implicitly convertible, and the outer key must always precede the equals keyword.

Syntax Implementations

The join clause can be expressed using either Query Expression Syntax or Method-Based Syntax (via the Enumerable.Join extension method).

Query Syntax:

var query = from outerItem in outerSequence
            join innerItem in innerSequence 
            on outerItem.Key equals innerItem.Key
            select new { outerItem, innerItem };

Method Syntax:

var query = outerSequence.Join(
    innerSequence,
    outerItem => outerItem.Key,          // Outer key selector
    innerItem => innerItem.Key,          // Inner key selector
    (outerItem, innerItem) => new { outerItem, innerItem } // Result selector
);

Join Variations

The standard join clause can be modified to produce different relational algebra operations.

1. Inner Join

This is the default behavior of the join clause. It returns a result only when there is a matching key in both the outer and inner sequences. Elements from either sequence lacking a corresponding match are omitted from the final projection.

2. Group Join

A group join correlates each element of the outer sequence with a collection of matching elements from the inner sequence, producing a hierarchical (1-to-many) result set. This is achieved by appending the into keyword to the join clause.

Query Syntax:

var groupJoinQuery = from outerItem in outerSequence
                     join innerItem in innerSequence 
                     on outerItem.Key equals innerItem.Key into groupedInner
                     select new { outerItem, GroupedItems = groupedInner };

Note: In method syntax, this is implemented using the Enumerable.GroupJoin method.

3. Left Outer Join

A left outer join returns all elements from the outer sequence, regardless of whether a match exists in the inner sequence. If no match exists, the inner sequence yields a default value (typically null for reference types). In C#, this is constructed by performing a group join and subsequently invoking the DefaultIfEmpty() method on the grouped sequence.

Query Syntax:

var leftOuterQuery = from outerItem in outerSequence
                     join innerItem in innerSequence 
                     on outerItem.Key equals innerItem.Key into groupedInner
                     from subItem in groupedInner.DefaultIfEmpty()
                     select new { outerItem, subItem };

4. Composite Keys

To join sequences based on multiple properties, you must project the keys into anonymous types. The compiler requires that the anonymous types on both sides of the equals keyword have identical property names, types, and declaration order to ensure they hash and equate correctly.

Query Syntax:

var compositeJoinQuery = from outerItem in outerSequence
                         join innerItem in innerSequence 
                         on new { outerItem.Key1, outerItem.Key2 } 
                         equals new { innerItem.Key1, innerItem.Key2 }
                         select new { outerItem, innerItem };

Master C# with Deep Grasping Methodology!

Learn More