Monday, July 27, 2009

Linq to SQL with left outer join

http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx

Linq to SQL
var query = from c in Customers
where c.CustomerID.StartsWith("b")
join o in Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
select new {CustomerID = c.CustomerID, ContactName=c.ContactName, OrderID=x.OrderID==null?-1:x.OrderID};

SQL
SELECT c.CustomerID
, c.ContactName
, (CASE WHEN o.OrderID IS NULL THEN @p1 ELSE o.OrderID END) AS value
FROM Customers c
LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID LIKE @p0

No comments:

Post a Comment