Saturday, February 19, 2011

LINQ syntax for left outer join

Hint : Use the DefaultIfEmpty() extension and the into contextual keyword

The trick is to store the result of the join into a collection and the select from the outer collection and the into 

e.g.:

var expr = from p in products 
               join o in customersOrders  
                    on p.IdProduct equals o.IdProduct
                    into orders
               
                    from order in orders.DefaultOrEmpty()
                         select new
                         {
                            p.IdProduct, 
                            Name = o.Name               
                         }


Notice that the only thing that makes it a left outer join is the use of into orders and then from using the orders.DefaultOrEmpty.

Ofcourse a right outer join is a left outer join with the collection orders swapped. 


Enjoy!