如何用Linq-To-Sql实现包含两个独立内连接的左外连接查询

Got it, let's walk through how to build this LINQ to SQL query to match your desired SQL output. The core challenge here is handling that nested inner join for the B tables, then left joining that combined result set to your A tables' inner join—so let's break it down step by step.

First, let's assume your LINQ to SQL DataContext has these entity classes mapped to your database tables:

• ParentTableA (maps to parenttableA)
• ChildTableA (maps to childtableA)
• ParentTableB (maps to parenttableB)
• ChildTableB (maps to childtableB)

Step-by-Step Implementation #

We'll split the query into logical parts to mirror your SQL structure:

1. Inner Join for A Tables
   This matches the parenttableA INNER JOIN childtableA part, plus the WHERE parenttableA.townid = 123 filter:

   var townId = 123;
   
   var aTableJoin = from parentA in db.ParentTableA
                    join childA in db.ChildTableA on parentA.Id equals childA.ParentId
                    where parentA.TownId == townId
                    select new { parentA, childA };
   

2. Inner Join for B Tables
   This creates the nested inner join subquery for parenttableB and childtableB:

   var bTableJoin = from parentB in db.ParentTableB
                    join childB in db.ChildTableB on parentB.Id equals childB.ParentId
                    select new { parentB, childB };
   

3. Left Outer Join the Two Result Sets
   Use LINQ's join ... into and DefaultIfEmpty() pattern to perform the left outer join, which ensures A table records are retained even if there's no matching B table data:

   var finalQuery = from a in aTableJoin
                    join b in bTableJoin on a.parentA.TownId equals b.parentB.TownId into bMatches
                    from bItem in bMatches.DefaultIfEmpty()
                    select new {
                        parentA = a.parentA.Name,
                        childA = a.childA.Name,
                        parentB = bItem?.parentB.Name, // Returns null if no match
                        childB = bItem?.childB.Name   // Returns null if no match
                    };
   

Compact Combined Version #

If you prefer a single query that mirrors your nested SQL structure more closely, you can inline the B table join as a subquery:

var townId = 123;

var finalQuery = from parentA in db.ParentTableA
                 join childA in db.ChildTableA on parentA.Id equals childA.ParentId
                 where parentA.TownId == townId
                 join bCombined in (
                     from parentB in db.ParentTableB
                     join childB in db.ChildTableB on parentB.Id equals childB.ParentId
                     select new { parentB, childB }
                 ) on parentA.TownId equals bCombined.parentB.TownId into bMatches
                 from bItem in bMatches.DefaultIfEmpty()
                 select new {
                     parentA = parentA.Name,
                     childA = childA.Name,
                     parentB = bItem?.parentB.Name,
                     childB = bItem?.childB.Name
                 };

What This Does #

When executed, this LINQ query will generate SQL almost identical to what you provided. The DefaultIfEmpty() ensures that even if there are no matching B table records for a given town, the A table records are still returned, with null values for the B table columns—exactly matching your sample result set.

内容的提问来源于stack exchange，提问作者Neil W