LINQ to SQL Left Outer Join

Ali Kazmi picture Ali Kazmi · Mar 31, 2009 · Viewed 149.8k times · Source

Is this query equivalent to a LEFT OUTER join?

//assuming that I have a parameter named 'invoiceId' of type int
from c in SupportCases
let invoice = c.Invoices.FirstOrDefault(i=> i.Id == invoiceId)
where (invoiceId == 0 || invoice != null)    
select new 
{
      Id = c.Id
      , InvoiceId = invoice == null ? 0 : invoice.Id
}

Answer

Amir picture Amir · Jan 19, 2011

You don't need the into statements:

var query = 
    from customer in dc.Customers
    from order in dc.Orders
         .Where(o => customer.CustomerId == o.CustomerId)
         .DefaultIfEmpty()
    select new { Customer = customer, Order = order } 
    //Order will be null if the left join is null

And yes, the query above does indeed create a LEFT OUTER join.

Link to a similar question that handles multiple left joins: Linq to Sql: Multiple left outer joins