Entity Framework with Multiple Joins and Subquery

Question

I have a complex query with multiple left joins and subqueries which I need to implement in Entify Framework. I've received the monster SQL and my goal is to do it on a elegant way with EF. The query consumes multiple tables and creates a "WITH" subquery on top which is included in the joins later. I've done a first approach with EF but when I inspect the output that EF sends to the DB, inner joins are sent when I am expecting LEFT JOINs.

A summary of the SQL follows:

WITH SUB_QUERY
     AS (  SELECT FIELD_A,
                    FIELD_B,
                    FIELD_C,
             MAX (FIELD_D) MAX_FIELD_D
             FROM TABLE_X
            WHERE SOME FIELD_A = 'WHATEVER'
         GROUP BY FIELD_A, FIELD_B, FIELD_C)
SELECT C.FIELD_A,
       C.FIELD_B,
       B.FIELD_X,
       D.FIELD_S,
       E.FIELD_J,
       F.FIELD_Y
FROM TABLE_A A
       LEFT JOIN SUB_QUERY B
          ON A.FIELD_C = B.FIELD_C
       LEFT JOIN TABLE_C C
          ON B.FIELD_A = C.FIELD_A
       LEFT JOIN TABLE_D D
          ON A.FIELD_C = D.FIELD_C
       LEFT JOIN TABLE_E E
          ON     A.FIELD_X = E.FIELD_X
       LEFT JOIN TABLE_F F
          ON A.FIELD_W = F.FIELD_W

WHERE A.FIELD_H = D.FIELD_H
       AND A.FIELD_D = B.MAX_FIELD_D

As you see, a subquery on top filters and groups some data to be consumed in a join below. Then all the joins take place and some fields are taken from different tables as the output of the query.

Which approach would you recommend me to accomplish this task? I've tried different approaches and no one of them works (either retrieve nothing, or many more rows than the SQL query on the DB, etc..)

Please note that the Domain Model in Entity Framework is properly setup: Primary Keys, collections, nested objects etc.. so I believe some of these joins are not even required because my EF entities contain already references to the child collections and parent objects (navigation properties).

Thanks a lot!!

if you give where condition like this WHERE A.FIELD_H = D.FIELD_H AND A.FIELD_D = B.MAX_FIELD_D it is like an inner join even if you are saying left join. You can add an and condition in the join if you need condition of left join. — Avi, May 03 '19 at 07:26
Nor really, without the where clause is creating inner joins too: I suspect is coz of the navigation properties, but not sure — Carlos, May 03 '19 at 07:38
if you look at the joins Table A is being joined with table B and then values of table b with C and not sure if it has to be table A on all cases as it is on D and E table. This will join with values of B and C so it may be one of the reason you are getting less records — Avi, May 03 '19 at 07:48
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) Those left joins could be replaced by inner joins, so you don't show that your LINQ is wrong. — philipxy, May 03 '19 at 08:22
You say "when I inspect the output that EF sends to the DB, inner joins are sent when I am expecting LEFT JOINs". You are getting inner joins, but you are also getting left joins. Your understanding of left join is wrong. Code questions require a [mcve]--cut & paste & runnable code & desired output & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, May 03 '19 at 08:27

score 0 · Answer 1 · answered May 03 '19 at 07:47

if you really need a left join you should mode the where condition related to a left joined table in the proper on clause

 FROM TABLE_A A
     LEFT JOIN SUB_QUERY B
        ON A.FIELD_C = B.FIELD_C
     LEFT JOIN TABLE_C C
        ON B.FIELD_A = C.FIELD_A
     LEFT JOIN TABLE_D D
        ON A.FIELD_C = D.FIELD_C AND A.FIELD_D = B.MAX_FIELD_D 
     LEFT JOIN TABLE_E E
        ON     A.FIELD_X = E.FIELD_X
     LEFT JOIN TABLE_F F
        ON A.FIELD_W = F.FIELD_W

the use of a left join table column in where force the relation to work as a INNER JOIN

Entity Framework with Multiple Joins and Subquery

1 Answers1