linq学习笔记（6）：Join操作
Join操作适用场景：在我们表关系中有一对一关系，一对多关系，多对多关系等。对各个表之间的关系，就用这些实现对多个表的操作。
在Join操作中，分别为Join(Join查询), SelectMany(Select一对多选择)和GroupJoin(分组Join查询)。
Join(Join查询):当一个query expression 有join字句时，而没有into字句，它将会被翻译成join方法。
描述：以Customers为主表,Orders为子表，用CustomerID 做关联进行join操作
语句：
from c in ndc.Customers
join o in ndc.Orders on c.CustomerID equals o.CustomerID
select new { c.CustomerID, o.EmployeeID };
对应SQL：
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的所有订单信息。
语句：
from c in ndc.Customers
from o in ndc.Orders
where c.City == "London"
select o;
对应SQL：
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表。
语句：
from p in db.Products
where p.Supplier.Country == "USA" && p.UnitsInStock == 0
select p;
对应SQL语句：
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.多对多关系
语句：
from e in db.Employees
from et in e.EmployeeTerritories
where e.City == "Seattle"
select new {e.FirstName, e.LastName, et.Territory.TerritoryDescription};
对应SQL语句：
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.自关联
语句：
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：
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.两表联合查询
语句：
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 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.三表联合
语句：
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 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）
语句：
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：
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
语句：
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语句：
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语句前就是符合语法的。上面的语句和下面这条是等价的。
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
语句：
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：
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。
关注 熵减黑客 ，一起学习成长
