left outer join Lambda

Question

All,

I have multiple left outer join from multiple tables. My SQL query works fine as below:

    select distinct
    C.CUST_CD, C.DSC, C.BH_PHONE, A.ADDRESS, CITY.DESCR CITY, ZIP.DESCR ZIP, 
    CSH.UDT_DT , CSR.DSC 
    from 
    dbo.CUST C
    inner join dbo.CUST_ADDR A on C.CUST_CD = A.CUST_CD and A.ADDR_CD = 5   
    left outer join dbo.LOCATION CITY on A.CITY_CD = CITY.LOCATION_CD
    left outer join dbo.LOCATION ZIP on A.ZIP_CD = ZIP.LOCATION_CD
    left outer join dbo.CUST_STS S on C.CUST_STS_CD = S.CUST_STS_CD
    left outer join dbo.CUST_STS_HIS CSH on C.CUST_CD = CSH.CUST_CD
    left outer join dbo.CUST_STS_REASON CSR on CSH.REASON_CD = CSR.REASON_CD
    where 
      C.CUST_STS_CD in (5)
      and CSH.CUST_STS_CD in (5)
      and CSR.STS_CD in (5)  
      order by C.CUST_CD

My Lambda expression looks like this:

    var items =    (
                    from a in db.CUSTs

                    from b in db.CUST_ADDR
                         .Where(bb => bb.CUST_CD == a.CUST_CD && bb.ADDR_CD ==5)                           

                    from c in db.LOCATION
                         .Where(cc => cc.LOCATION_CD == b.CITY_CD)
                         .DefaultIfEmpty() // <== makes outer left join         

                    from d in db.LOCATION
                         .Where(dd => dd.LOCATION_CD == c.LOCATION_CD)
                         .DefaultIfEmpty()

                    from e in db.LOCATION
                        .Where(ee => ee.LOCATION_CD == b.ZIP_CD)
                        .DefaultIfEmpty() 

                    from f in db.CUST_STS_HIS
                       .Where(ff => ff.CUST_STS_CD == a.CUST_STS_CD)
                       .DefaultIfEmpty() 

                    from g in db.CUST_STS_REASON
                       .Where(gg => gg.STS_CD == f.CUST_STS_CD)
                       .DefaultIfEmpty() 

                    where (a.CUST_STS_CD == 5 && f.CUST_STS_CD == 5 && g.STS_CD == 5)

                    select new
                    {                           
                        CUSTCode = a.CUST_CD,
                        CUSTDesc = a.DSC,
                        CUSTPhone = a.BH_PHONE,
                        CUSTAddr = a.ADDRESS,
                        CUSTCity = c.DESCR,
                        CUSTZip = d.DESCR,
                        CUSTCounty = e.DESCR,
                        Date = f.UDT_DT,
                        reason = g.DSC

                    })
                    .ToList();

Here's the SQL from the Lamda expression:

    SELECT 
1 AS [C1], 
[Filter1].[CUST_CD1] AS [CUST_CD], 
[Filter1].[DSC] AS [DSC], 
[Filter1].[BH_PHONE] AS [BH_PHONE], 
[Filter1].[ADDRESS] AS [ADDRESS], 
[Filter1].[DESCR1] AS [DESCR], 
[Filter1].[DESCR2] AS [DESCR1], 
[Filter1].[DESCR3] AS [DESCR2], 
[Filter1].[UDT_DT] AS [UDT_DT], 
[Extent7].[DSC] AS [DSC1]
FROM   (SELECT [Extent1].[CUST_CD] AS [CUST_CD1], [Extent1].[DSC] AS [DSC], [Extent1].[BH_PHONE] AS [BH_PHONE], [Extent1].[ADDRESS] AS [ADDRESS], [Extent3].[DESCR] AS [DESCR1], [Extent4].[DESCR] AS [DESCR2], [Extent5].[DESCR] AS [DESCR3], [Extent6].[UDT_DT] AS [UDT_DT], [Extent6].[CUST_STS_CD] AS [CUST_STS_CD1]
    FROM      [DBO].[CUST] AS [Extent1]
    INNER JOIN (SELECT 
[CUST_ADDR].[CUST_CD] AS [CUST_CD], 
[CUST_ADDR].[ADDR_CD] AS [ADDR_CD], 
[CUST_ADDR].[ADDRESS] AS [ADDRESS], 
[CUST_ADDR].[CITY_CD] AS [CITY_CD], 
[CUST_ADDR].[ZIP_CD] AS [ZIP_CD], 
[CUST_ADDR].[STATE_CD] AS [STATE_CD], 
[CUST_ADDR].[ADDRESS2] AS [ADDRESS2]
FROM [DBO].[CUST_ADDR] AS [CUST_ADDR]) AS [Extent2] ON ([Extent2].[CUST_CD] = [Extent1].[CUST_CD]) AND (cast(5 as decimal(18)) = [Extent2].[ADDR_CD])
    LEFT OUTER JOIN [DBO].[LOCATION] AS [Extent3] ON [Extent3].[LOCATION_CD] = [Extent2].[CITY_CD]
    LEFT OUTER JOIN [DBO].[LOCATION] AS [Extent4] ON [Extent4].[LOCATION_CD] = [Extent3].[LOCATION_CD]
    LEFT OUTER JOIN [DBO].[LOCATION] AS [Extent5] ON [Extent5].[LOCATION_CD] = [Extent2].[ZIP_CD]
    INNER JOIN (SELECT 
[CUST_STS_HIS].[CUST_CD] AS [CUST_CD], 
[CUST_STS_HIS].[UDT_DT] AS [UDT_DT], 
[CUST_STS_HIS].[REASON_CD] AS [REASON_CD], 
[CUST_STS_HIS].[CUST_STS_CD] AS [CUST_STS_CD], 
[CUST_STS_HIS].[USR_ID] AS [USR_ID], 
[CUST_STS_HIS].[NOTES] AS [NOTES]
FROM [DBO].[CUST_STS_HIS] AS [CUST_STS_HIS]) AS [Extent6] ON [Extent6].[CUST_STS_CD] = [Extent1].[CUST_STS_CD]
    WHERE (cast(5 as decimal(18)) = [Extent1].[CUST_STS_CD]) AND (cast(5 as decimal(18)) = [Extent6].[CUST_STS_CD]) ) AS [Filter1]
INNER JOIN (SELECT 
[CUST_STS_REASON].[STS_CD] AS [STS_CD], 
[CUST_STS_REASON].[REASON_CD] AS [REASON_CD], 
[CUST_STS_REASON].[DSC] AS [DSC], 
[CUST_STS_REASON].[RES_KEY] AS [RES_KEY]
FROM [DBO].[CUST_STS_REASON] AS [CUST_STS_REASON]) AS [Extent7] ON [Extent7].[STS_CD] = [Filter1].[CUST_STS_CD1]

I am getting my SQL query results as expected (about 300 or so records). However, my lambda is bringing all records (over 100k). It is complete ignoring my "where" clause in the statement. Could you please point me in the right direction on this query? I appreciate your time and consideration.

You can always check what is the sql generated from EF and see the difference. check this answer https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — Juan Carlos Oropeza, Aug 15 '18 at 18:03
@GuillaumeCR - it is with the "from b" like this: from b in db.RET_ADDR .Where(bb => bb.RET_CD == a.RET_CD && bb.ADDR_CD == 5) . Still brings about 76k records — 1moreLearner, Aug 15 '18 at 18:21
Are you sure? It looks like the snippet you added in your comment doesn't exist in the code you posted. As a matter of fact there is no RET_ADDR anywhere in your code. — Guillaume CR, Aug 15 '18 at 18:42
@1moreLearner It's hard to believe EF6 would ever bring the whole table in memory w/o you explicitly requesting that via `AsEnumerable` or something similar. If this is real EF6 query, then if you replace `ToList()` with `ToString()` you should be able to get the generated SQL. Can you include it in the question? — Ivan Stoev, Aug 15 '18 at 18:56
@GuillaumeCR - Yes. I had copied the wrong version. I did realize that the condition was missing and had added to it. I appreciate you pointing that out though. — 1moreLearner, Aug 15 '18 at 19:17
Are we looking at the right version? If so, the ADDR_CD = 5 predicate is still missing, which may explain the discrepancy. — Guillaume CR, Aug 15 '18 at 19:20
@GuillaumeCR - Unfortunetaly, it is still passing more results than what I expect. — 1moreLearner, Aug 15 '18 at 20:39
@IvanStoev - The "ToString();" it does not give me the SQL. And here's the EF version: EntityFramework {6.2.0} . — 1moreLearner, Aug 15 '18 at 20:45
@IvanStoev, I was able to get the SQL, please see edited post. It does look like the "f" table is not being converted to a left outer join. Thanks. — 1moreLearner, Aug 15 '18 at 21:01
It's ok, because some of the original SQL `where` conditions effectively turn some of the `left outer join`s to `inner join`. So EF query is definitely not pulling the whole tables in memory. Looks like the LINQ query is missing the `distinct` operator from your SQL query though. So how about adding `.Distinct()` before `.ToList()`? — Ivan Stoev, Aug 15 '18 at 22:05

score 0 · Answer 1 · answered Aug 16 '18 at 14:12

Well, since there is more than one way of skinning a cat, I ended up creating, executing a Stored Procedure that solved the problem and returned only the data that I needed. Thank you for all your responses though. I really learned something new here. I appreciate!

left outer join Lambda

1 Answers1