在 C# 3.0入門系列（四）-之Select操作 一文中，我們提到了query expression首先會(huì)被翻譯成標(biāo)準(zhǔn)的API, 而dlinq在join操作中，一共為我們提供了三個(gè)API.它們是Join, SelectMany和GroupJoin
Join
在101 的sample中，并沒(méi)有join的例子。當(dāng)一個(gè)query expression 有join字句時(shí)，而沒(méi)有into字句，它將會(huì)被翻譯成join方法。如，以Customers為主表,Orders為子表，用CustomerID 做關(guān)聯(lián)進(jìn)行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?});
該語(yǔ)句所產(chǎn)生的T-sql語(yǔ)句為
SELECT
?
[
t0
]
.
[
CustomerID
]
,?
[
t1
]
.
[
EmployeeID
]
FROM
?
[
Customers
]
?
AS
?
[
t0
]
INNER
?
JOIN
?
[
Orders
]
?
AS
?
[
t1
]
?
ON
?
[
t0
]
.
[
CustomerID
]
?
=
?
[
t1
]
.
[
CustomerID
]
SelectMany
在101sample中，給了4個(gè)SelectMany的例子。會(huì)被翻譯成SelectMany需要滿足2個(gè)條件。1，query語(yǔ)句中沒(méi)有join和into，2，必須出現(xiàn)EntitySet。 關(guān)于EntitySet,請(qǐng)參考 C#3.0進(jìn)階系列（一）-從映射講起
先看第一個(gè)例子
????
var
?q?
=
????????
from
?c?
in
?db.Customers
????????
from
?o?
in
?c.Orders
????????
where
?c.City?
==
?"London"
????????
select
?o;
[Association(Name = " Order_OrderDetail " ,?Storage = " _OrderDetails " ,?OtherKey = " OrderID " )]
[Association(Name = " Order_OrderDetail " ,?Storage = " _Order " ,?ThisKey = " OrderID " ,?IsForeignKey = true )]
所以，你就不用擔(dān)心，dlinq是否知道該按那個(gè)鍵進(jìn)行關(guān)聯(lián)。有興趣的朋友，可以自己修改這里的OtherKey和ThisKey的值，看看翻譯的T-sql語(yǔ)句是否變了。
第二個(gè)例子
????
var
?q?
=
????????
from
?p?
in
?db.Products
????????
where
?p.Supplier.Country?
==
?"USA"?
&&
?p.UnitsInStock?
==
?
0
????????
select
?p;
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
????
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};
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
????????};
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
GroupJoin
當(dāng)dlinq語(yǔ)句中，有join而且還有into時(shí)，它會(huì)被翻譯為GroupJoin.我們先來(lái)看第一個(gè)例子。
????
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的概念是對(duì)其結(jié)果進(jìn)行重新命名。為什么需要重新命名呢？我們以本例為例。One To Many的關(guān)系中，左邊是one，它每條記錄叫做c（from c in db.Customers），右邊是many，其每條記錄叫做o ( join o in db.Orders )，每對(duì)應(yīng)左邊的一個(gè)c，都會(huì)有一組o，那這一組o，就叫做orders，也就是說(shuō)，我們把一組o命名為orders，這就是into用途。（和groupby中類似）。這也就是為什么在select語(yǔ)句中，orders可以調(diào)用聚合函數(shù)Count。
在這個(gè)例子中，翻譯的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很聰明，直接用其內(nèi)欠的t-sql返回值作為字段值。
第二個(gè)例子
????
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
()};
三個(gè)表聯(lián)合查詢。在其join語(yǔ)句后，緊跟著又是一個(gè)join.只是表多了些，并沒(méi)有太多新鮮的東西。
第三個(gè)例子
????
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的標(biāo)準(zhǔn)寫法。以Employees為左表，Orders 為右，Orders 表中為空時(shí)，填沖null值。在將join的結(jié)果重命名后，再使用DefaultEmpty()函數(shù)，對(duì)其再次查詢。大家需要注意的時(shí)，其最后的結(jié)果中有個(gè)Order，因?yàn)閒rom o in ords.DefaultIfEmpty() 是對(duì)ords組再一次遍歷，所以，最后結(jié)果中的Order并不是一個(gè)集合。但是，如果沒(méi)有from o in ords.DefaultIfEmpty() 這句，最后的select語(yǔ)句寫成select new { e.FirstName, e.LastName, Order = ords }的話，那Order就是一個(gè)集合
上例翻譯的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
第四個(gè)例子，let語(yǔ)句
????
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語(yǔ)句有點(diǎn)類似into，也是個(gè)重命名的概念。需要提醒大家的是，let只要是放在第一個(gè)from后，select語(yǔ)句前就是符合語(yǔ)法的。上面的語(yǔ)句和下面這條是等價(jià)的。
????????????
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?};
其產(chǎn)生的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
它也應(yīng)該和下面的語(yǔ)句等價(jià)，但其翻譯的T-sql語(yǔ)句稍微有所不同。
????????????
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?};
有興趣的朋友可以研究下，其產(chǎn)生的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
第五個(gè)例子為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};
這里，它使用三個(gè)表，并且用匿名類來(lái)表示它們之間的關(guān)系。因?yàn)椋渲g的關(guān)系已經(jīng)不是一個(gè)鍵可以描述清楚的，所以只有用匿名類，表示組合鍵。這個(gè)例子有點(diǎn)像SelectMany中的ManyToMany的那個(gè)。
還有一種composite key的，就是兩個(gè)表之間是用composite key表示關(guān)系的。這種情況很簡(jiǎn)單，不需像該例中使用匿名類。該例翻譯的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
最后一個(gè)例子，沒(méi)有看出什么好玩的來(lái)，不講了。
寫到這里，c#3.0的入門系列已經(jīng)接近尾聲了。我們一起學(xué)習(xí)了Dlinq的最基本操作。還剩下Union, In, Like還有一些聚合函數(shù)等操作。將會(huì)在下面幾章中介紹。不知道大家對(duì)什么還感興趣的，或者我能夠提供幫助的，盡管問(wèn)。
關(guān)于Linq To Sql 中的，Create, update, Delete 操作，以及Store procedure 及UDF等，更像是運(yùn)用函數(shù)，而不是語(yǔ)言。所以，不在C#語(yǔ)言中講。在考慮是不是開(kāi)個(gè)什么Linq To Sql的深入應(yīng)用。
寫blog是對(duì)自己個(gè)人知識(shí)的總結(jié)，也是對(duì)自己表達(dá)功底的考驗(yàn)。因本人水平有限，錯(cuò)誤再所難免，還請(qǐng)大家指出并諒解。
TrackBack: http://www.cnblogs.com/126/archive/2007/07/12/815331.html
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
