December 2009 - Posts

Linq2Sql: LEFT OUTER JOIN with multiple columns
Fri, Dec 4 2009 10:13 AM 
 
I learned from the website HookedOnLinq.com the way to do a LEFT OUTER JOIN with multiple columns. 

var query = from c in Customers
join o in Orders on
new {Col1 = c.CustomerID, Col2 = c.CountryCode}
equals
new {Col1 = o.CustomerID, Col2 = o.CountryCode}
into g
from o in g.DefaultIfEmpty()
select new {c.CustomerID, c.CountryCode, OrderId = (o == null ? null : o.OrderId)};

 

However, I found another way to do this: 

var query = from c in Customers
join o in Orders on o.CustomerID equals c.CustomerID into g
from o in g.Where(item => i.CountryCode == c.CountryCode).DefaultIfEmpty()
select new {c.CustomerID, c.CountryCode, OrderId = (o == null ? null : o.OrderId)};

I found this later here as well…

by Dries Marckmann | with no comments
Filed under: , ,