Linq 复合from子句和SelectMany扩展方法

复合的from子句

如果需要根据对象的一个成员进行筛选,而该成员本身是一个系列,就可以使用复合的from子句

以Northwind数据库中的Orders和Order Details为例，筛选出Orders表中，其子表Order Details中ProductID字段为11的记录

SELECT  t1.* 
FROM Orders t1 INNER JOIN dbo.[Order Details] t2 ON t1.OrderID = t2.OrderID 
WHERE t2.ProductID = 11
ORDER BY t1.OrderID

注意：

这里使用的是INNER内联查询，而不是外联

在Northwind数据库中，每一个Orders记录的子表Order Details里，ProductID是唯一的，否则，以上语句会使同一个Orders记录出现多次

SELECT t1.*而非*

使用复合的form子句实现如下

var query = from o in dbContext.Orders
		from od in o.Order_Details
		where od.ProductID == 11
		orderby o.OrderID
		select o;

Linq生成的Sql如下

SELECT 
	[Extent1].[OrderID] AS [OrderID], 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[EmployeeID] AS [EmployeeID], 
	[Extent1].[OrderDate] AS [OrderDate], 
	[Extent1].[RequiredDate] AS [RequiredDate], 
	[Extent1].[ShippedDate] AS [ShippedDate], 
	[Extent1].[ShipVia] AS [ShipVia], 
	[Extent1].[Freight] AS [Freight], 
	[Extent1].[ShipName] AS [ShipName], 
	[Extent1].[ShipAddress] AS [ShipAddress], 
	[Extent1].[ShipCity] AS [ShipCity], 
	[Extent1].[ShipRegion] AS [ShipRegion], 
	[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
	[Extent1].[ShipCountry] AS [ShipCountry]
	FROM  [dbo].[Orders] AS [Extent1]
	INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]
	WHERE 11 = [Extent2].[ProductID]
	ORDER BY [Extent1].[OrderID] ASC

这里，复合form子句被转换成了一个内联查询，内联条件ON根据主外键约束自动生成

复合form子句用于生成联接查询，不同形式的复合form子句生成不同的联接查询

CROSS JOIN

var query = from o in dbContext.Orders
			  from od in o.Order_Details
			  select o;

Linq生成的Sql如下

SELECT 
	[Extent1].[OrderID] AS [OrderID], 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[EmployeeID] AS [EmployeeID], 
	[Extent1].[OrderDate] AS [OrderDate], 
	[Extent1].[RequiredDate] AS [RequiredDate], 
	[Extent1].[ShippedDate] AS [ShippedDate], 
	[Extent1].[ShipVia] AS [ShipVia], 
	[Extent1].[Freight] AS [Freight], 
	[Extent1].[ShipName] AS [ShipName], 
	[Extent1].[ShipAddress] AS [ShipAddress], 
	[Extent1].[ShipCity] AS [ShipCity], 
	[Extent1].[ShipRegion] AS [ShipRegion], 
	[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
	[Extent1].[ShipCountry] AS [ShipCountry]
	FROM  [dbo].[Orders] AS [Extent1]
	INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]

INNER JOIN

var query = from o in dbContext.Orders
			from od in o.Order_Details
			select o;

Linq生成的Sql如下

<pre name="code" class="sql">SELECT 
	[Extent1].[OrderID] AS [OrderID], 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[EmployeeID] AS [EmployeeID], 
	[Extent1].[OrderDate] AS [OrderDate], 
	[Extent1].[RequiredDate] AS [RequiredDate], 
	[Extent1].[ShippedDate] AS [ShippedDate], 
	[Extent1].[ShipVia] AS [ShipVia], 
	[Extent1].[Freight] AS [Freight], 
	[Extent1].[ShipName] AS [ShipName], 
	[Extent1].[ShipAddress] AS [ShipAddress], 
	[Extent1].[ShipCity] AS [ShipCity], 
	[Extent1].[ShipRegion] AS [ShipRegion], 
	[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
	[Extent1].[ShipCountry] AS [ShipCountry]
	FROM  [dbo].[Orders] AS [Extent1]
	INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]

LEFT OUTER JOIN

var query = from o in dbContext.Orders
		from od in o.Order_Details.DefaultIfEmpty()
		select o;

Linq生成的Sql如下

SELECT 
	[Extent1].[OrderID] AS [OrderID], 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[EmployeeID] AS [EmployeeID], 
	[Extent1].[OrderDate] AS [OrderDate], 
	[Extent1].[RequiredDate] AS [RequiredDate], 
	[Extent1].[ShippedDate] AS [ShippedDate], 
	[Extent1].[ShipVia] AS [ShipVia], 
	[Extent1].[Freight] AS [Freight], 
	[Extent1].[ShipName] AS [ShipName], 
	[Extent1].[ShipAddress] AS [ShipAddress], 
	[Extent1].[ShipCity] AS [ShipCity], 
	[Extent1].[ShipRegion] AS [ShipRegion], 
	[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
	[Extent1].[ShipCountry] AS [ShipCountry]
	FROM  [dbo].[Orders] AS [Extent1]
	LEFT OUTER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]

此外，还可以在form子句中使用where条件，来增加联接的ON条件

var query = from o in dbContext.Orders
		from od in o.Order_Details.Where(od => od.ProductID == 11)
		select o;

Linq生成的Sql如下

SELECT 
	[Extent1].[OrderID] AS [OrderID], 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[EmployeeID] AS [EmployeeID], 
	[Extent1].[OrderDate] AS [OrderDate], 
	[Extent1].[RequiredDate] AS [RequiredDate], 
	[Extent1].[ShippedDate] AS [ShippedDate], 
	[Extent1].[ShipVia] AS [ShipVia], 
	[Extent1].[Freight] AS [Freight], 
	[Extent1].[ShipName] AS [ShipName], 
	[Extent1].[ShipAddress] AS [ShipAddress], 
	[Extent1].[ShipCity] AS [ShipCity], 
	[Extent1].[ShipRegion] AS [ShipRegion], 
	[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
	[Extent1].[ShipCountry] AS [ShipCountry]
	FROM  [dbo].[Orders] AS [Extent1]
	INNER JOIN [dbo].[Order Details] AS [Extent2] ON ([Extent1].[OrderID] = [Extent2].[OrderID]) AND (11 = [Extent2].[ProductID])

复合from子句实际上就是联接查询，大部分复合from子句（并不是所有，如cross join）的Linq完全可以用join子句的Linq来重写，两者生成的Sql也相同，推荐使用join Linq，这种写法与Sql更接近，更易读

var query1 = from o in dbContext.Orders
			from od in o.Order_Details
			select o;


var query2 = from o in dbContext.Orders
			 join od in dbContext.Order_Details
			 on o.OrderID equals od.OrderID
			 select o;

SelectMany()扩展方法

C#编译器把复合的from子句和LINQ查询转换为SelectMany()扩展方法，SelectMany()方法可用于迭代序列的序列

SelectMany()的其中一个重载版本如下，可以看到，它是一个扩展方法

public static IEnumerable<TResult> SelectMany<TSource, TResult>(
    this IEnumerable<TSource> source,
    Func<TSource, IEnumerable<TResult>> selector
)

类型参数
TSource
source 中的元素的类型。

TResult
selector 返回的序列元素的类型。

参数
source
类型：System.Collections.Generic.IEnumerable<TSource>
一个要投影的值序列。

selector
类型：System.Func<TSource, IEnumerable<TResult>>
应用于每个元素的转换函数。

返回值
类型：System.Collections.Generic.IEnumerable<TResult>
一个 IEnumerable<T>，其元素是对输入序列的每个元素调用一对多转换函数的结果。

SelectMany<TSource, TResult>(IEnumerable<TSource>, Func<TSource, IEnumerable<TResult>>) 方法枚举输入序列，使用转换函数将每个元素映射到 IEnumerable<T>，然后枚举并生成每个这种 IEnumerable<T> 对象的元素。
也就是说，对于 source 的每个元素，selector 被调用，返回一个值的序列。 然后 SelectMany<TSource, TResult>(IEnumerable<TSource>, Func<TSource, IEnumerable<TResult>>) 将集合的此二维集合合并为一维 IEnumerable<T> 并将其返回。

var query = dbContext.Orders.OrderBy(o => o.OrderID).SelectMany(o => o.Order_Details);

Linq生成的Sql如下

SELECT 
	[Extent1].[OrderID] AS [OrderID], 
	[Extent1].[ProductID] AS [ProductID], 
	[Extent1].[UnitPrice] AS [UnitPrice], 
	[Extent1].[Quantity] AS [Quantity], 
	[Extent1].[Discount] AS [Discount]
	FROM [dbo].[Order Details] AS [Extent1]

此外

var query = dbContext.Orders.Where(o => o.OrderID == 10248).OrderBy(o => o.OrderID).SelectMany(o => o.Order_Details);

Linq生成的Sql如下

SELECT 
    [Extent1].[OrderID] AS [OrderID], 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[UnitPrice] AS [UnitPrice], 
    [Extent1].[Quantity] AS [Quantity], 
    [Extent1].[Discount] AS [Discount]
    FROM [dbo].[Order Details] AS [Extent1]
    WHERE 10248 = [Extent1].[OrderID]

var query = dbContext.Orders.Where(o => o.OrderID == 10248).OrderBy(o => o.OrderID).SelectMany(o => o.Order_Details);

Linq生成的Sql如下

SELECT 
    [Extent2].[OrderID] AS [OrderID], 
    [Extent2].[ProductID] AS [ProductID], 
    [Extent2].[UnitPrice] AS [UnitPrice], 
    [Extent2].[Quantity] AS [Quantity], 
    [Extent2].[Discount] AS [Discount]
    FROM  [dbo].[Orders] AS [Extent1]
    INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]
    WHERE (5 = [Extent1].[EmployeeID]) AND ([Extent1].[EmployeeID] IS NOT NULL)

var query = dbContext.Orders.OrderBy(o => o.OrderID).SelectMany(o => o.Order_Details);

这条查询，Select列都是Order_Detials表的，而最初的需求是列出Order表列，这时，就需要用到SelectMany()方法的另一个重载

public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult>(
	this IEnumerable<TSource> source,
	Func<TSource, IEnumerable<TCollection>> collectionSelector,
	Func<TSource, TCollection, TResult> resultSelector
)

类型参数
TSource
source 中的元素的类型。

TCollection
collectionSelector 收集的中间元素的类型。

TResult
结果序列的元素的类型。

参数
source类型：System.Collections.Generic.IEnumerable<TSource>
一个要投影的值序列。

collectionSelector类型：System.Func<TSource, IEnumerable<TCollection>>
一个应用于输入序列的每个元素的转换函数。

resultSelector类型：System.Func<TSource, TCollection, TResult>
一个应用于中间序列的每个元素的转换函数。

返回值
类型：System.Collections.Generic.IEnumerable<TResult>
一个 IEnumerable<T>，其元素是对 source 的每个元素调用一对多转换函数 collectionSelector，然后将那些序列元素中的每一个及其相应的源元素映射为结果元素的结果。

var query = dbContext.Orders.OrderBy(o => o.OrderID).SelectMany(o => o.Order_Details, (o, od) => new {Order = o, Order_Detail = od});

Linq生成的Sql如下

SELECT 
    [Extent1].[OrderID] AS [OrderID], 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[OrderDate] AS [OrderDate], 
    [Extent1].[RequiredDate] AS [RequiredDate], 
    [Extent1].[ShippedDate] AS [ShippedDate], 
    [Extent1].[ShipVia] AS [ShipVia], 
    [Extent1].[Freight] AS [Freight], 
    [Extent1].[ShipName] AS [ShipName], 
    [Extent1].[ShipAddress] AS [ShipAddress], 
    [Extent1].[ShipCity] AS [ShipCity], 
    [Extent1].[ShipRegion] AS [ShipRegion], 
    [Extent1].[ShipPostalCode] AS [ShipPostalCode], 
    [Extent1].[ShipCountry] AS [ShipCountry], 
    [Extent2].[OrderID] AS [OrderID1], 
    [Extent2].[ProductID] AS [ProductID], 
    [Extent2].[UnitPrice] AS [UnitPrice], 
    [Extent2].[Quantity] AS [Quantity], 
    [Extent2].[Discount] AS [Discount]
    FROM  [dbo].[Orders] AS [Extent1]
    INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]参考资料

参考资料

C#高级编程第7版

from 子句（C# 参考）
https://msdn.microsoft.com/zh-cn/library/bb383978.aspx

如何：执行自定义联接操作（C# 编程指南）
https://msdn.microsoft.com/zh-cn/library/bb882533.aspx

Enumerable.SelectMany 方法 
https://msdn.microsoft.com/zh-cn/library/system.linq.enumerable.selectmany(v=VS.100).aspx

LINQ TO SQL中的selectMany
http://www.cnblogs.com/ASPNET2008/archive/2008/12/19/1357690.html

Linq使用心得——SelectMany替代二重foreach循环
http://www.cnblogs.com/manupstairs/archive/2012/11/27/2790114.html