.Net Linq in Entity Framework 4 left outer join with isnull equivalent in where clause

To do a left outer join, please review blog http://smehrozalam.wordpress.com/2009/06/10/c-left-outer-joins-with-linq/ it explains it very well.

The problem I had was trying to get the left outer join table and use the value if it was there in the where clause.

Below is the sql I was going for..

   1: SELECT le.qty 

   2: FROM LinesExpected le

   3:     LEFT OUTER JOIN

   4:         LineReceipts lr

   5:             ON(le.LineExpectedID = lr.LineExpectedID)

   6: WHERE 

   7:     le.qty > isnull(lr.qty,0)

   8:     AND le.LineExpectedID != currentLineExpectedID

   9:     AND le.custid = "DM616"

  10:     AND le.itemid = "PI-29"

  11:     AND le.active = 1

And the linq that produces a left outer join and is able to perform the isnull equivalent looks like..

   1: var sql = (from le in LinesExpecteds

   2:  from lr in LineReceipts.Where(lr=>lr.LineExpectedID == le.LineExpectedID).DefaultIfEmpty()

   3:  where 

   4:     le.CustID == "DM616" && 

   5:     le.Active == true && 

   6:     le.ItemID == "pi-29" && 

   7:     le.LineExpectedID != 0 &&

   8:     le.Qty > (lr == null ? 0 : lr.Qty)

   9:  select le.Qty).ToList();

Notice line 8, that is where the magic occurs, the main table LinesExpected (le) is compared to either lr.Qty if it exist or Zero if it does not exist.

 

I got the idea from http://stackoverflow.com/questions/413084/equivalent-of-sql-isnull-in-linq but the answer was using the isnull in the select piece of the query so I thought I would blog about how to use similar idea in the where clause.

 

The TSQL generated (via LINQPad 4) is..

   1: SELECT 

   2: [Extent1].[Qty] AS [Qty]

   3: FROM  [dbo].[LinesExpected] AS [Extent1]

   4: LEFT OUTER JOIN [dbo].[LineReceipts] AS [Extent2] ON [Extent2].[LineExpectedID] = [Extent1].[LineExpectedID]

   5: WHERE (N'DM616' = [Extent1].[CustID]) AND (1 = [Extent1].[Active]) AND (N'pi-29' = [Extent1].[ItemID]) AND (0 <> [Extent1].[LineExpectedID]) AND ([Extent1].[Qty] > (CASE WHEN ([Extent2].[LineReceiptID] IS NULL) THEN cast(0 as decimal(18)) ELSE [Extent2].[Qty] END))