linq学习笔记(6):Join操作

长平狐 发布于 2013/06/17 12:54
阅读 289
收藏 0

     Join操作适用场景:在我们表关系中有一对一关系,一对多关系,多对多关系等。对各个表之间的关系,就用这些实现对多个表的操作。

     在Join操作中,分别为Join(Join查询), SelectMany(Select一对多选择)和GroupJoin(分组Join查询)。

     Join(Join查询):当一个query exssion 有join字句时,而没有into字句,它将会被翻译成join方法。

     描述:以Customers为主表,Orders为子表,用CustomerID 做关联进行join操作

     语句:

            var q  =
                from c 
in  ndc.Customers
                join o 
in  ndc.Orders on c.CustomerID equals o.CustomerID
                select 
new  { c.CustomerID, o.EmployeeID };

     对应SQL:

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

     说明:join方法的第一个参数,为子表,第二个参数,表示主表中的选择键,第三个参数为子表中的对应键,第四个为最终筛选结果。需要注意的时,因为参数的顺序是确定的,所以在写linq语句时,c.CustomerID equals o.CustomerID 的顺序是不能变的。

     SelectMany:我们在写查询语句时,如果被翻译成SelectMany需要满足2个条件。1:查询语句中没有join和into,2:必须出现EntitySet。在我们表关系中有一对一关系,一对多关系,多对多关系等,下面分别介绍一下。

     1.一对多关系

     描述:本示例使用外键,在订单中查询客户所在城市为London的所有订单信息。

     语句:

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

     对应SQL:

SELECT   [ 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 ] . [ Customers ]   AS   [ t0 ] [ dbo ] . [ Orders ]   AS   [ t1 ]
WHERE  ( [ t0 ] . [ City ]   =   @p0 AND  ( [ t1 ] . [ CustomerID ]   =   [ t0 ] . [ CustomerID ] )
--  @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

     说明:Customers与Orders是一对多关系。即Orders在Customers类中以EntitySet形式出现。所以第二个from是从c.Orders而不是ndc.Orders里进行筛选。

     描述:本示例使用外键,在WHERE子句中,查询产品供应商是在美国的,在市场上的产品。使用了p.Supplier.Country条件,间接关联了Supplier表。

     语句:

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

     对应SQL语句:

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 NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
--
 @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

     2.多对多关系

     语句:

    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};

     对应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 NVarChar (Size = 7; Prec = 0; Scale = 0) [Seattle]

     说明:多对多关系一般会涉及三个表(如果有一个表是自关联的,那有可能只有2个表)。这一句语句涉及Employees, EmployeeTerritories, Territories三个表。它们的关系是1:M:1。Employees和Territories没有很明确的关系。

     3.自关联

     语句:

    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
        };

     对应SQL:

SELECT   [ t0 ] . [ FirstName ]   AS   [ FirstName1 ] [ t0 ] . [ LastName ]   AS   [ LastName1 ] [ 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 ] )

     GroupJoin:没有join和into,被翻译成SelectMany,同时有join和into时,那么就被翻译为GroupJoin。在这里into的概念是对其结果进行重新命名。

     1.两表联合查询

     语句:

            var q  =
                from c 
in  ndc.Customers
                join o 
in  ndc.Orders on c.CustomerID equals o.CustomerID into orders
                select 
new  { c.ContactName, OrderCount  =  orders.Count() };

     对应SQL语句:

SELECT   [ t0 ] . [ ContactName ] , (
    
SELECT   COUNT ( * )
    
FROM   [ dbo ] . [ Orders ]   AS   [ t1 ]
    
WHERE   [ t0 ] . [ CustomerID ]   =   [ t1 ] . [ CustomerID ]
    ) 
AS   [ OrderCount ]
FROM   [ dbo ] . [ Customers ]   AS   [ t0 ]

     说明:一对多的关系中,左边是一,它每条记录叫做c(from c in db.Customers),右边是多,其每条记录叫做o ( join o in db.Orders ),每对应左边的一个c,都会有一组o,那这一组o,就叫做orders,也就是说,我们把一组o命名为orders,这就是into用途。这也就是为什么在select语句中,orders可以调用聚合函数Count。 
     2.三表联合

     语句:

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

     对应SQL语句:

SELECT   [ t0 ] . [ ContactName ] , (
    
SELECT   COUNT ( * )
    
FROM   [ dbo ] . [ Orders ]   AS   [ t1 ]
    
WHERE   [ t0 ] . [ CustomerID ]   =   [ t1 ] . [ CustomerID ]
    ) 
AS   [ ords ] , (
    
SELECT   COUNT ( * )
    
FROM   [ dbo ] . [ Employees ]   AS   [ t2 ]
    
WHERE   [ t0 ] . [ City ]   =   [ t2 ] . [ City ]
    ) 
AS   [ emps ]
FROM   [ dbo ] . [ Customers ]   AS   [ t0 ]

     说明:三个表联合查询。在其join语句后,紧跟着又是一个join.只是表多了些。

     3.左外连接(Left Out Join)

     语句:

            var q  =
                from em 
in  ndc.Employees
                join o 
in  ndc.Orders on em equals o.Employees into ords
                from o 
in  ords.DefaultIfEmpty()
                select 
new  { em.FirstName, em.LastName, order  =  o };

     对应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 ]

 

     说明: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就是一个集合。
     4.Projected let assignment

     语句:

    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};

     对应SQL语句:

SELECT   [ t1 ] . [ ContactName ] [ t2 ] . [ OrderID ] [ t1 ] . [ value ]   AS   [ z ]
FROM  (
    
SELECT   [ t0 ] . [ CustomerID ] [ t0 ] . [ ContactName ] [ t0 ] . [ City ]   +   [ t0 ] . [ Country ]   AS   [ value ]
    
FROM   [ dbo ] . [ Customers ]   AS   [ t0 ]
    ) 
AS   [ t1 ]
CROSS   JOIN   [ dbo ] . [ Orders ]   AS   [ t2 ]
WHERE   [ t1 ] . [ CustomerID ]   =   [ t2 ] . [ CustomerID ]

     说明:let语句有点类似into,也是个重命名的概念。需要提醒大家的是,let只要是放在第一个from后,select语句前就是符合语法的。上面的语句和下面这条是等价的。

            var q  =
                from c 
in  ndc.Customers
                let z 
=  c.City  +  c.Country
                join o 
in  ndc.Orders on c.CustomerID equals o.CustomerID into ords
                from o 
in  ords
                select 
new  { c.ContactName, o.OrderID, z };

     5.composite key

     语句:

            var q  =
                from o 
in  ndc.Orders
                from p 
in  ndc.Products
                join d 
in  ndc.Order_Details 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 };

     对应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 ] )

     说明:使用三个表,并且用匿名类来表示它们之间的关系。因为,其之间的关系已经不是一个键可以描述清楚的,所以只有用匿名类,表示组合键。

     今天就学这些吧,明天继续,晚上研究Entity Framework。


原文链接:http://www.cnblogs.com/peida/archive/2008/08/15/1266218.html
加载中
返回顶部
顶部