LINQ, GroupJoin, and Left Outer Joins

Filed in Software Developement Leave a comment

This is a topic which involves me scouring the internet every time I need to do this task. outer joins in LINQ are one of the few LINQ concepts that is not very simple to execute.

Essentially, an Outer Join maps to GroupJoin. GroupJoin is almost identical to Join, except that your right side lambda parameter is a collection (of potentially zero items) instead of an object reference. Because it is a collection it doesn’t really help you out if you need to do a real left outer join. To solve this we make use of SelectMany and DefaultIfEmpty LINQ methods.

SelectMany will allow us to expand the collection, and DefaultIfEmpty will retain the empty collections, together we can supply a value of null (or whatever you want) to a column that is not joined. Below is an example to illustrate.

Customers
    .GroupJoin(Orders, x => x.CustomerID, x => x.CustomerID, (a, b) => new { a.Name, b })
    .SelectMany(x => x.b.DefaultIfEmpty(), (a, b) => a.Name, OrderID = (b == null ? (int?)null : b.OrderID)

, , , ,

TOP