C# 3.0入門系列（十）-之Join操作

本節講join操作。我們知道，T-sql中，有三種最基本的join，inner join, left join, 和right join。 而dlinq並不支持right join。道理很簡單，right join以right表為基礎，left表中沒有對應記錄的，將以null值填充。而dlinq以left表做為主表創建對象。如果一個對象為null,你如何獲取它的其他的屬性呢？

在C# 3.0入門系列（四）-之Select操作一文中，我們提到了query expression首先會被翻譯成標准的API, 而dlinq在join操作中，一共為我們提供了三個API.它們是Join, SelectMany和GroupJoin

Join

在101 的sample中，並沒有join的例子。當一個query expression 有join字句時，而沒有into字句，它將會被翻譯成join方法。如，以Customers為主表,Orders為子表，用CustomerID 做關聯進行join操作。

var q =
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID

select new { c.CustomerID, o.EmployeeID };

它將會被翻譯成

var q = db.Customers.Join(db.Orders, c => c.CustomerID, o => o.CustomerID, (c, o) => new { c.CustomerID, o.EmployeeID });

join方法的第一個參數，為子表，第二個參數，表示主表中的選擇鍵，第三個參數為子表中的對應鍵，第四個為最終篩選結果。大家需要注意的時，因為參數的順序是確定的，所以在寫dlinq語句時，c.CustomerID equals o.CustomerID 的順序是不能變的。

該語句所產生的T-sql語句為

SELECT [t0].[CustomerID], [t1].[EmployeeID]
FROM [Customers] AS [t0]
INNER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]

SelectMany

在101sample中，給了4個SelectMany的例子。會被翻譯成SelectMany需要滿足2個條件。1，query語句中沒有join和into，2，必須出現EntitySet。 關於EntitySet,請參考C#3.0進階系列（一）-從映射講起

先看第一個例子

var q =
from c in db.Customers
from o in c.Orders
where c.City == "London"
select o;

Customers與Orders是1:M的關系。即Orders在Customers類中，以EntitySet出現。所以第二個from是從c.Orders而不是db.Orders裡進行篩選。定義了他們關系的Mapping Code用Attribute保存了他們的關系。如

[Association(Name="Order_OrderDetail", Storage="_OrderDetails", OtherKey="OrderID")]
[Association(Name="Order_OrderDetail", Storage="_Order", ThisKey="OrderID", IsForeignKey=true)]

所以，你就不用擔心，dlinq是否知道該按那個鍵進行關聯。有興趣的朋友，可以自己修改這裡的OtherKey和ThisKey的值，看看翻譯的T-sql語句是否變了。

第二個例子

var q =
from p in db.Products
where p.Supplier.Country == "USA" && p.UnitsInStock == 0
select p;

這個例子，直接就使用了p.Supplier.Country 做條件，這樣，也間接關聯了Supplier表。該語句生成的T-sql語句更是值得揣摩，這大概是Left Out Join 的最簡單的Dlinq語句。

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Suppliers] AS [t1] ON [t1].[SupplierID] = [t0].[SupplierID]
WHERE ([t1].[Country] = @p0) AND ([t0].[UnitsInStock] = @p1)
-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

第三個例子是M : M的關系

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

M:M的關系，一般會涉及三個表。（如果，有一個表是自關聯的，那有可能只有2個表。）在這裡，涉及Employees, EmployeeTerritories, Territories共三個表。它們的關系是1 : M : 1. Employees和Territories沒有很明確的關系。這個例子和上一個不同的是，它是在Select字句中，牽扯到Territories表。其生成的T-sql為

SELECT [t0].[FirstName], [t0].[LastName], [t2].[TerritoryDescription]
FROM [dbo].[Employees] AS [t0]
CROSS JOIN [dbo].[EmployeeTerritories] AS [t1]
INNER JOIN [dbo].[Territories] AS [t2] ON [t2].[TerritoryID] = [t1].[TerritoryID]
WHERE ([t0].[City] = @p0) AND ([t1].[EmployeeID] = [t0].[EmployeeID])
-- @p0: Input String (Size = 7; Prec = 0; Scale = 0) [Seattle]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

最後一個例子是自關聯的，並且夾帶了條件

var q =
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
};

其T-sql為

SELECT [t0].[FirstName], [t0].[LastName], [t1].[FirstName] AS [FirstName2], [t1].[LastName] AS [LastName2], [t0].[City]
FROM [dbo].[Employees] AS [t0], [dbo].[Employees] AS [t1]
WHERE ([t0].[City] = [t1].[City]) AND ([t1].[ReportsTo] = [t0].[EmployeeID])
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

從上面的例子我們可以看出，Dlinq以非常靈活的方式，處理其內部各表的關系。它不須顯式的聲明需要關聯到那個表，也可以放在Where和Select等子句中，隱式關聯。

GroupJoin

當dlinq語句中，有join而且還有into時，它會被翻譯為GroupJoin.我們先來看第一個例子。

var q =
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()};

本系列曾在C#3.0入門系列（八）-之GroupBy操作 一文中，第一次談到到into。into的概念是對其結果進行重新命名。為什麼需要重新命名呢？我們以本例為例。One To Many的關系中，左邊是one，它每條記錄叫做c（from c in db.Customers），右邊是many，其每條記錄叫做o ( join o in db.Orders )，每對應左邊的一個c，都會有一組o，那這一組o，就叫做orders，也就是說，我們把一組o命名為orders，這就是into用途。（和groupby中類似）。這也就是為什麼在select語句中，orders可以調用聚合函數Count。

在這個例子中，翻譯的t-sql為

SELECT [t0].[ContactName], (
　　SELECT COUNT(*)
　　FROM [dbo].[Orders] AS [t1]
　　WHERE [t0].[CustomerID] = [t1].[CustomerID]
　　) AS [value]
FROM [dbo].[Customers] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

dlinq很聰明，直接用其內欠的t-sql返回值作為字段值。

第二個例子

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

第五個例子為composite key.

var q =
from o in db.Orders
from p in db.Products
join d in db.OrderDetails
　on new {o.OrderID, p.ProductID} equals new {d.OrderID, d.ProductID}
　into details
from d in details
select new {o.OrderID, p.ProductID, d.UnitPrice};

這裡，它使用三個表，並且用匿名類來表示它們之間的關系。因為，其之間的關系已經不是一個鍵可以描述清楚的，所以只有用匿名類，表示組合鍵。這個例子有點像SelectMany中的ManyToMany的那個。

還有一種composite key的，就是兩個表之間是用composite key表示關系的。這種情況很簡單，不需像該例中使用匿名類。該例翻譯的T-sql為

SELECT [t0].[OrderID], [t1].[ProductID], [t2].[UnitPrice]
FROM [dbo].[Orders] AS [t0], [dbo].[Products] AS [t1], [dbo].[Order Details] AS [t2]
WHERE ([t0].[OrderID] = [t2].[OrderID]) AND ([t1].[ProductID] = [t2].[ProductID])
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

最後一個例子，沒有看出什麼好玩的來，不講了。

寫到這裡，c#3.0的入門系列已經接近尾聲了。我們一起學習了Dlinq的最基本操作。還剩下Union, In, Like還有一些聚合函數等操作。將會在下面幾章中介紹。不知道大家對什麼還感興趣的，或者我能夠提供幫助的，盡管問。

關於Linq To Sql 中的，Create, update, Delete 操作，以及Store procedure 及UDF等，更像是運用函數，而不是語言。所以，不在C#語言中講。在考慮是不是開個什麼Linq To Sql的深入應用。

寫blog是對自己個人知識的總結，也是對自己表達功底的考驗。因本人水平有限，錯誤再所難免，還請大家指出並諒解。