LINQ to SQL 連接查詢大全：Join / SelectMany / GroupJoin 實戰模式

三大連接操作符對比

SelectMany：導航屬性隱式連接

1:N (Customer → Orders)

from c in db.Customers
from o in c.Orders  // 導航屬性 EntitySet
where c.City == "London"
select o;

跨表導航篩選 (Product → Supplier)

from p in db.Products
where p.Supplier.Country == "USA" && p.UnitsInStock == 0
select p;  // 生成 LEFT OUTER JOIN Suppliers

M:N (Employee ↔ Territory 中間表 EmployeeTerritories)

from e in db.Employees
from et in e.EmployeeTerritories
where e.City == "Seattle"
select new { e.FirstName, e.LastName, et.Territory.TerritoryDescription };

自關聯 (Employee → ReportsTo)

from e1 in db.Employees
from e2 in e1.Employees
where e1.City == e2.City
select new { 
    FirstName1 = e1.FirstName, LastName1 = e1.LastName,
    FirstName2 = e2.FirstName, LastName2 = e2.LastName,
    e1.City
};

GroupJoin：顯式分組連接 (join...into)

雙向連接：客戶 + 訂單計數

from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into orders
select new { c.ContactName, OrderCount = orders.Count() };

生成 SQL：子查詢 SELECT COUNT(*) FROM Orders WHERE CustomerID = c.CustomerID。

三向連接：客戶 + 訂單數 + 同城員工數

from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into ords
join e in db.Employees on c.City equals e.City into emps
select new { c.ContactName, ords = ords.Count(), emps = emps.Count() };

左外連接：DefaultIfEmpty()

from e in db.Employees
join o in db.Orders on e.EmployeeID equals o.EmployeeID into ords
from o in ords.DefaultIfEmpty()
select new { e.FirstName, e.LastName, Order = o };

無訂單員工的 Order 為 null。

Let 賦值投影

let z = c.City + c.Country
from o in ords
select new { c.ContactName, o.OrderID, z };

組合鍵連接

join d in db.OrderDetails
on new { o.OrderID, p.ProductID } equals new { d.OrderID, d.ProductID }

可空/不可空鍵匹配

on o.EmployeeID equals (int?)e.EmployeeID

選型指南

• 實體間有導航屬性 → 首選 SelectMany (語義清晰)
• 無導航屬性或需精確控制 JOIN 類型 → Join/GroupJoin
• 需聚合統計 (Count/Sum) → GroupJoin + into 最自然
• 左外連接必須用 GroupJoin + DefaultIfEmpty()