c# linq join

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

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

     Join(Join查询):当一个query expression 有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])


©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值