LINQ Inner and Outer Joins in C# Query Syntax

This guide explains how to perform inner joins and left outer joins in C# using Language Integrated Query (LINQ) query syntax. By using standard LINQ clauses such as join, equals, into, and the DefaultIfEmpty() method, you can effectively combine data from multiple collections based on matching keys.

Sample Data Structure

To illustrate these joins, consider two basic collections: Department and Employee.

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

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

var departments = new List<Department>
{
    new Department { Id = 1, Name = "Engineering" },
    new Department { Id = 2, Name = "Human Resources" },
    new Department { Id = 3, Name = "Marketing" } // No employees
};

var employees = new List<Employee>
{
    new Employee { Id = 101, Name = "Alice", DepartmentId = 1 },
    new Employee { Id = 102, Name = "Bob", DepartmentId = 1 },
    new Employee { Id = 103, Name = "Charlie", DepartmentId = 2 },
    new Employee { Id = 104, Name = "Diana", DepartmentId = 99 } // Department does not exist
};

Performing an Inner Join

An inner join returns only the records where the join key matches in both the left and right collections. In LINQ query syntax, this is achieved using the join and equals keywords.

var innerJoinQuery = 
    from emp in employees
    join dept in departments on emp.DepartmentId equals dept.Id
    select new
    {
        EmployeeName = emp.Name,
        DepartmentName = dept.Name
    };

foreach (var item in innerJoinQuery)
{
    Console.WriteLine($"{item.EmployeeName} - {item.DepartmentName}");
}

Output:

Alice - Engineering
Bob - Engineering
Charlie - Human Resources

Note: Diana (unmatched department) and the Marketing department (no employees) are excluded from the results.

Performing a Left Outer Join

A left outer join returns all records from the left (first) collection, along with matching records from the right (second) collection. If there is no match on the right, the right-side element becomes null (or the default value for its type).

In LINQ, a left outer join requires:

Performing a group join using the into keyword.
Flattening the group using from ... in group.DefaultIfEmpty().

var leftOuterJoinQuery = 
    from emp in employees
    join dept in departments on emp.DepartmentId equals dept.Id into deptGroup
    from dept in deptGroup.DefaultIfEmpty()
    select new
    {
        EmployeeName = emp.Name,
        DepartmentName = dept != null ? dept.Name : "No Department"
    };

foreach (var item in leftOuterJoinQuery)
{
    Console.WriteLine($"{item.EmployeeName} - {item.DepartmentName}");
}

Output:

Alice - Engineering
Bob - Engineering
Charlie - Human Resources
Diana - No Department

Note: Because DefaultIfEmpty() supplies null when a match is missing, you must handle potential null values when projecting the results.