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] = @p0AND ([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] = @p0AND ([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是对自己个人知识的总结,也是对自己表达功底的考验。因本人水平有限,错误再所难免,还请大家指出并谅解。

posted @ 2007-07-12 18:57  Tom Song  阅读(10725)  评论(24编辑  收藏  举报