Sunday, April 05, 2009

Left Joins for 1 to 0..1 relationships in Linq

Here is an example of doing a ‘left join’ between two classes with a 1 to 0..1 relationship between them.

We have an Employee class.

public class Employee
{
    public Employee(int id, int? bossId)
    {
         Id = id;
         BossId = bossId;
    }

    public int Id { get; set; }
    public int? BossId { get; set; }
}

And a collection of employees.

var employees = new[] {
    new Employee(1, null),
    new Employee(2, 1),
    new Employee(3, 2),
    new Employee(4, 2)
};

Each employee has 0 or 1 bosses.

Now to get a list of each employee and his boss, we can use the following linq query. (We want employee 1 to be in the list with a null boss.)

var result =
from employee in employees
join boss in employees
on employee.BossId equals boss.Id
into employeeBosses
select new {
    Employee = employee,
    Boss = employeeBosses.SingleOrDefault()
};

The trick is to perform a group join on the employee and his bosses and then use SingleOrDefault to collapse employeeBosses to a single item or null.