Friday 16 January 2009

Left outer join in Linq to Sql

Here's how to do a left outer join in sql. So in sql we would have:

SELECT c.Name, o.Price
FROM Customer c LEFT OUTER JOIN Order o ON c.Id = o.CustomerId

This becomes in Linq:

from c in DataContext.Customers
join o in DataContext.Orders on c.Id equals o.CustomerId into customerOrders
from co in customerOrders.DefaultIfEmpty()
select new
{
c.Name,
co.Price
}

The trick is in the DefaultIfEmpty which will join a null Order object onto the Customer as would happen with a left outer join.

No comments: