Translating Relasional Joins into LINQ

Working with relational daatsets in .NET often requires translating standard SQL set operations into LINQ expressions. While inner joins map directly to built-in methods, outer and cross joins require specific patterns involving grouping, flattening, and Cartesian product generation. The following sections demonstrate how to implement each join type using both query syntax and method syntax.

Domain Models and Test Data

These examples utilize two entity classes representing organizational units and personnel.

public class Department
{
    public int Id { get; set; }
    public string DeptName { get; set; }
}

public class StaffMember
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public int? AssignedDeptId { get; set; }
}

The seed dataset contains three departments and three employees. One employee has a null department assignment to properly demonstrate outer join behavior.

1. Inner Join

Filters results to include only records where the key exists in both collections.

SQL Equivalent:

SELECT d.DeptName, s.FullName 
FROM Departments d 
INNER JOIN Staff s ON d.Id = s.AssignedDeptId

LINQ Query Syntax:

from dept in Departments
join emp in Staff on dept.Id equals emp.AssignedDeptId
select new { dept.DeptName, emp.FullName }

LINQ Method Syntax:

Departments.Join(
    Staff, 
    primary => primary.Id, 
    secondary => secondary.AssignedDeptId, 
    (primary, secondary) => new { primary.DeptName, secondary.FullName }
)

2. Left Outer Join

Preserves all elements from the primary collection. Matching elements from the secondary collection are included, or a default value is used when no match occurs.

SQL Equivalent:

SELECT d.DeptName, ISNULL(s.FullName, 'Unassigned') AS FullName 
FROM Departments d 
LEFT JOIN Staff s ON d.Id = s.AssignedDeptId

LINQ Query Syntax:

from dept in Departments
join emp in Staff on dept.Id equals emp.AssignedDeptId into tempGroup
from current in tempGroup.DefaultIfEmpty()
select new { dept.DeptName, FullName = current?.FullName ?? "Unassigned" }

LINQ Method Syntax:

Departments.GroupJoin(
    Staff, 
    primary => primary.Id, 
    secondary => secondary.AssignedDeptId, 
    (primary, group) => new { primary, group }
).SelectMany(
    composite => composite.group.DefaultIfEmpty(), 
    (composite, current) => new { composite.primary.DeptName, FullName = current?.FullName ?? "Unassigned" }
)

3. Right Outer Join

LINQ does not expose a dedicated right-join operator. This is achieved by reversing the collection order and applyign left-join logic.

SQL Equivalent:

SELECT ISNULL(d.DeptName, 'No Department') AS DeptName, s.FullName 
FROM Staff s 
LEFT JOIN Departments d ON s.AssignedDeptId = d.Id

LINQ Query Syntax:

from emp in Staff
join dept in Departments on emp.AssignedDeptId equals dept.Id into tempGroup
from current in tempGroup.DefaultIfEmpty()
select new { DeptName = current?.DeptName ?? "No Department", emp.FullName }

LINQ Method Syntax:

Staff.GroupJoin(
    Departments, 
    primary => primary.AssignedDeptId, 
    secondary => secondary.Id, 
    (primary, group) => new { primary, group }
).SelectMany(
    composite => composite.group.DefaultIfEmpty(), 
    (composite, current) => new { DeptName = current?.DeptName ?? "No Department", composite.primary.FullName }
)

4. Full Outer Join

Returns all records from both collections, aligning matches and filling non-matches with defaults. Since LINQ lacks a native full-join method, simulate it by concatenating left and right outer join results, then applying a distinct filter.

LINQ Query Syntax:

var leftSide = from dept in Departments
               join emp in Staff on dept.Id equals emp.AssignedDeptId into g1
               from current1 in g1.DefaultIfEmpty()
               select new { dept.DeptName, FullName = current1?.FullName ?? "Unassigned" };

var rightSide = from emp in Staff
                join dept in Departments on emp.AssignedDeptId equals dept.Id into g2
                from current2 in g2.DefaultIfEmpty()
                select new { DeptName = current2?.DeptName ?? "No Department", emp.FullName };

leftSide.Concat(rightSide).Distinct();

LINQ Method Syntax:

Departments.GroupJoin(Staff, d => d.Id, e => e.AssignedDeptId, (d, g) => new { d, g })
    .SelectMany(x => x.g.DefaultIfEmpty(), (x, e) => new { x.d.DeptName, FullName = e?.FullName ?? "Unassigned" })
    .Concat(
        Staff.GroupJoin(Departments, e => e.AssignedDeptId, d => d.Id, (e, g) => new { e, g })
            .SelectMany(x => x.g.DefaultIfEmpty(), (x, d) => new { DeptName = d?.DeptName ?? "No Department", x.e.FullName })
    )
    .Distinct();

5. Cross Join

Produces a Cartesian product, pairing every element from the first collection with every element from the second.

SQL Equivalent:

SELECT d.DeptName, s.FullName FROM Departments d, Staff s

LINQ Query Syntax:

from dept in Departments
from emp in Staff
select new { dept.DeptName, emp.FullName }

LINQ Method Syntax:

Departments.SelectMany(
    _ => Staff, 
    (primary, secondary) => new { primary.DeptName, secondary.FullName }
)