本節講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
()};
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
????
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
()};
????
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};
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
????
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};
????????????
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?};
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
????????????
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?};
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
????
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};
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入門系列（四）-之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?});
該語句所產生的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;
[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;
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
當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是對自己個人知識的總結，也是對自己表達功底的考驗。因本人水平有限，錯誤再所難免，還請大家指出并諒解。
TrackBack: http://www.cnblogs.com/126/archive/2007/07/12/815331.html
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
