SQL to LINQ - Left Join Before Inner Join

Question

So I have a SQL query that I would like to convert to LINQ.

Here is said query:

SELECT *
FROM DatabaseA.SchemaA.TableA ta
    LEFT OUTER JOIN DatabaseA.SchemaA.TableB tb
    ON tb.ShipId = ta.ShipId
    INNER JOIN DatabaseA.SchemaA.TableC tc
    ON tc.PostageId= tb.PostageId
WHERE tc.PostageCode = 'Package'
      AND ta.MailId = 'Specification'

The problem I am struggling with is I cannot seem to figure out how to do a left join in LINQ before an inner join, since doing a left join in LINQ is not as clear to me at least.

I have found numerous examples of a LINQ inner join and then a left join, but not left join and then inner join.

If it helps, here is the LINQ query I have been playing around with:

var query = from m in tableA
join s in tableB on m.ShipId equals s.ShipId into queryDetails
from qd in queryDetails.DefaultIfEmpty()
join p in tableC on qd.PostageId equals p.PostageId
where m.MailId == "Specification" && p.PostageCode == "Package"
select m.MailId;

I have tried this a few different ways but I keep getting an "Object reference not set to an instance of an object" error on qd.PostageId.

LINQ is very new to me and I love learning it, so any help on this would be much appreciated. Thanks!

Yeah I think so! I have yet to implement it yet because I'm away from my laptop, but upon reading through the answers make sense. I will be sure to test tomorrow and select the best answer. Thanks guys! — wibby35, Mar 15 '18 at 02:19

NetMage · Answer 1 · 2018-04-05T22:40:10.167

From my SQL conversion recipe:

JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where
JOIN conditions that are multiple ANDed equality tests between the two tables should be translated into anonymous objects
LEFT JOIN is simulated by using into joinvariable and doing another from from the joinvariable followed by .DefaultIfEmpty().

The order of JOIN clauses doesn't change how you translate them:

var ans = from ta in TableA
          join tb in TableB on ta.ShipId equals tb.ShipId into tbj
          from tb in tbj.DefaultIfEmpty()
          join tc in TableC on tb.PostageId equals tc.PostageId
          where tc.PostageCode == "Package" && ta.MailId == "Specification"
          select new { ta, tb, tc };

However, because the LEFT JOIN is executed before the INNER JOIN and then the NULL PostageIds in TableB for unmatched rows will never match any row in TableC, it becomes equivalent to an INNER JOIN as well, which translates as:

var ans2 = from ta in tableA
           join tb in tableB on ta.ShipId equals tb.ShipId
           join tc in tableC on tb.PostageId equals tc.PostageId
           where tc.PostageCode == "Package" && ta.MailId == "Specification"
           select new { ta, tb, tc };

mjwills · Accepted Answer · 2018-03-15T00:15:55.290

1

Use:

var query = from m in tableA
join s in tableB on m.ShipId equals s.ShipId
join p in tableC on s.PostageId equals p.PostageId
where m.MailId == "Specification" && p.PostageCode == "Package"
select m.MailId;

Your query uses a LEFT OUTER JOIN but it doesn't need it.

It will, in practice, function as an INNER JOIN due to your tc.PostageCode = 'Package' clause. If you compare to a column value in a table in a WHERE clause (and there are no OR clauses and you aren't comparing to NULL) then effectively all joins to get to that table will be treated as INNER).

That clause will never be true if TableB is null (which is why you use LEFT OUTER JOIN vs INNER JOIN) - so you should just use an INNER JOIN to make the problem simpler.

edited Mar 15 '18 at 00:15

answered Mar 14 '18 at 21:48

mjwills

23,389
6
40
63

I don't see how `tc.PostageCode` affects the `OUTER JOIN` of `TableB`, though your conclusion is correct. Also, if you do `into queryDetails` you can't reference `s` after that (it won't compile). – NetMage Mar 14 '18 at 21:58
If you removed the `WHERE` clause in the SQL, the `JOIN` between `TableA` and `TableB` is still _effectively_ an `INNER JOIN` because there is an `INNER JOIN` between `TableB` and `TableC` - the `WHERE` clause has nothing to do with it. – NetMage Mar 14 '18 at 22:26
That is true @NetMage, and certainly a valid way to look at it. Here is another way. Let's say the query was `SELECT * FROM DatabaseA.SchemaA.TableA ta LEFT OUTER JOIN DatabaseA.SchemaA.TableB tb ON tb.ShipId = ta.ShipId LEFT OUTER JOIN DatabaseA.SchemaA.TableC tc ON tc.PostageId= tb.PostageId WHERE tc.PostageCode = 'Package' AND ta.MailId = 'Specification'` instead. In that case, both `LEFT OUTER JOIN`s are effectively INNER due to the WHERE clause. That is what I was trying to teach the OP. – mjwills Mar 14 '18 at 23:17
That is true, but not the query :) – NetMage Mar 14 '18 at 23:42

SQL to LINQ - Left Join Before Inner Join

2 Answers2