第二個例子
var q =
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()};
三個表聯合查詢。在其join語句後，緊跟著又是一個join.只是表多了些，並沒有太多新鮮的東西。
第三個例子
var q =
from e in db.Employees
join o in db.Orders on e equals o.Employee into ords
from o in ords.DefaultIfEmpty()
select new {e.FirstName, e.LastName, Order = o};
Left Out Join的標准寫法。以Employees為左表，Orders 為右，Orders 表中為空時，填沖null值。在將join的結果重命名後，再使用DefaultEmpty()函數，對其再次查詢。大家需要注意的時，其最後的結果中有個Order，因為from o in ords.DefaultIfEmpty() 是對ords組再一次遍歷，所以，最後結果中的Order並不是一個集合。但是，如果沒有from o in ords.DefaultIfEmpty() 這句，最後的select語句寫成select new { e.FirstName, e.LastName, Order = ords }的話，那Order就是一個集合
上例翻譯的T-sql 為
SELECT [t0].[FirstName], [t0].[LastName], [t2].[test], [t2].[OrderID], [t2].[CustomerID], [t2].[EmployeeID], [t2].[OrderDate], [t2].[RequiredDate], [t2].[ShippedDate], [t2].[ShipVia], [t2].[Freight], [t2].[ShipName], [t2].[ShipAddress], [t2].[ShipCity], [t2].[ShipRegion], [t2].[ShipPostalCode], [t2].[ShipCountry]
FROM [dbo].[Employees] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]
FROM [dbo].[Orders] AS [t1]
) AS [t2] ON [t0].[EmployeeID] = [t2].[EmployeeID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0
第四個例子，let語句
var q =
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into ords
let z = c.City + c.Country
from o in ords
select new {c.ContactName, o.OrderID, z};
let語句有點類似into，也是個重命名的概念。需要提醒大家的是，let只要是放在第一個from後，select語句前就是符合語法的。上面的語句和下面這條是等價的。
var q =
from c in db.Customers
let z = c.City + c.Country
join o in db.Orders on c.CustomerID equals o.CustomerID into ords
from o in ords
select new { c.ContactName, o.OrderID, z };
其產生的T-sql均為：
SELECT [t1].[ContactName], [t2].[OrderID], [t1].[value]
FROM (
SELECT [t0].[City] + [t0].[Country] AS [value], [t0].[CustomerID], [t0].[ContactName]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
CROSS JOIN [dbo].[Orders] AS [t2]
WHERE [t1].[CustomerID] = [t2].[CustomerID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0
它也應該和下面的語句等價，但其翻譯的T-sql語句稍微有所不同。
var q =
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into ords
from o in ords
let z = c.City + c.Country
select new { c.ContactName, o.OrderID, z };
有興趣的朋友可以研究下，其產生的T-sql 為
SELECT [t2].[ContactName], [t2].[OrderID], [t2].[value]
FROM (
SELECT [t0].[City] + [t0].[Country] AS [value], [t0].[CustomerID], [t0].[ContactName], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID2]
FROM [Customers] AS [t0], [Orders] AS [t1]
) AS [t2]
WHERE [t2].[CustomerID] = [t2].[CustomerID2]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0
