C#3.0入门系列（十）-之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 });

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

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

)]
[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};

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

     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

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