Linq-to-Sql LEFT OUTER JOIN between 2 separate INNER JOINRS

Question

Trying to generate the following SQL from Linq-To-Sql

SELECT 
parenttableA.Name AS parentA,
childtableA.Name AS childA,
parenttableB.Name AS parentB,
childtableB.Name AS childB

FROM parenttableA
INNER JOIN childtableA ON childtableA.parentid = parenttableA.id

LEFT OUTER JOIN

(
parenttableB
INNER JOIN childtableB ON childtableB.parentid = parenttableB.id
)

ON parenttableB.townid = parenttableA.townid

WHERE parenttableA.townid = 123

This SQL should return something like:

parentA    childA    parentB    childB
=======    ======    =======    ======
John       Dave      Paul       Mark
Jim        John      (null)     (null)

So, in other words:

How can I have 2 separate inner joins linked together with a LEFT OUTER JOIN so that I still get "A" records from town 123, even if no "B" records exist for that town?

Perhaps my [LINQ to SQL Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) may be helpful. — NetMage, May 21 '18 at 17:27

score 1 · Accepted Answer · edited Jun 09 '20 at 21:03

1

This should work for IQueryable for enumerable you should add null checks

var query = from ptA in parenttableA
            join ctA in childtableA on ptA.Id equals ctA.Id
            join ptBctB in (from ptB in parenttableB
                            join ctB in childtableB on ptB.Id equals ctB.Id
                            select new { ptB, ctB }) on ptA.TownId equals ptBctB.ptB.TownId into ptBGroup
            from ptBctBLeft in ptBGroup.DefaultIfEmpty()
            where ptA.TownId == 123
            select new
                   {
                       parentA = ptA.Name,
                       childA = ctA.Name,
                       parentB = ptBctBLeft.ptB.Name,
                       childB = ptBctBLeft.ctB.Name,
                   };

Also I think you can do this with just mapping configuration and have this as a property in your POCO but you need to post the EF config for that.

edited Jun 09 '20 at 21:03

marc_s

732,580
175
1,330
1,459

answered May 21 '18 at 15:50

Filip Cordas

2,531
1
12
23

Many thanks, Filip. I can now get the resultset I'm after. Only thing I noticed was the generated SQL created a SELECT statement for the ptBGroup, whereas the SQL in my original message does not. I wonder if the SELECT statement in the generated SQL impairs performance at all? – Neil W May 22 '18 at 01:17
And thanks also for the variable names you've used. ...Left, ...Group. Helped a lot with understanding. – Neil W May 22 '18 at 01:23
@NeilW I don't think it will have an impact on performance but I am not sure. You might want to run a profiler on the query if you want the max performance but I think query optimizer will handle this. If you have big issues with this I think you will need to run the raw sql but I don't think it will be needed. – Filip Cordas May 22 '18 at 08:12

Linq-to-Sql LEFT OUTER JOIN between 2 separate INNER JOINRS

1 Answers1