How to get an outerjoin on a Linq query

Question

Given this linq query

from c in context.Customers
from o in c.Orders
where c.City == "MyCity" || o.ShipTo == "MyCity"
select c

the query will not return any rows if the customer's city is "MyCity" but does not have any orders. This is because of the implied inner join between Customers and Orders. How do I select customers with a City of "MyCity" or order shipped to "MyCity

In this case I need an outer join between Customers and Orders. How do I express that in Linq? I think the approximate TSQL would be

select customers.* 
from customers 
left join orders on customers.id = orders.customerid
where customers.city = 'MyCity' or orders.ShipTo = 'MyCity'

Answer 1

To get an outer join, you can use DefaultIfEmpty. See this question: http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins

from c in context.Customers
from o in context.Orders
    .Where(a => a.customerid == c.id)
    .DefaultIfEmpty()
where c.City == "MyCity" || o.ShipTo == "MyCity"
select c

Alternatively, you can do this:

from c in context.Customers
join o in context.Orders on c.id equals o.customerid into g
from x in g.DefaultIfEmpty() 
where c.City == "MyCity" || x.ShipTo == "MyCity"
select c

I believe they both generate the same SQL.

Answer 2

You have to use DefaultIfEmpty

I think something like this would work

var result = from c in context.Customers
    join o in c.Orders on c.CustomerId equals o.CustomerId into lj
    from or in lj.DefaultIfEmpty()
    where c.City == "MyCity" || or.ShipTo == "MyCity"
    select c