Linq SelectMany

SelectMany

SelectMany查询语法

from 标识符1  in 可枚举的表达式1
from 标识符2  in 可枚举的表达式2

先看一个简单的例子

string[] arr = { "a b c", "x y z", "1 2 3" };
            var q1 = from a in arr
                from b in a.Split()
                select b;
// 方法语法
var q1 = arr.SelectMany(a => a.Split());
  
foreach (var item in q1)
            {
                Console.WriteLine(item);
            }
// a-b-c-x-y-z-1-2-3-

将嵌套的结构展平化

假如使用arr.Select(a => a.Split());，会发现得到的是层次化的结果

var q2 = from a in arr
            from b in a.Split()
            orderby a, b
            select b + " come from " + a;

转换成方法语法

       var q2 = arr.SelectMany(a => a.Split(), (a, b) => new { a, b })
                .OrderBy(@t => @t.a)
                .ThenBy(@t => @t.b)
                .Select(@t => @t.b + " come from " + @t.a);

SelectMany CROSS JOIN

var q1 = from c in context.Customers
                from p in context.Purchases
                select c.Name +" buy "+ p.Price;
                
//SELECT (COALESCE([c].[Name], N'') + N' buy ') + CAST([p].[Price] AS nvarchar(max))
FROM [Customers] AS [c]
CROSS JOIN [Purchases] AS [p]
WHERE [c].[Id] = [p].[CustomerId]

SelectMany INNER JOIN

如果采用导航属性

 var q1 = from c in context.Customers
                from p in c.Purchases
                select c.Name +" buy "+ p.Price;
                
// SELECT (COALESCE([c].[Name], N'') + N' buy ') + CAST([p].[Price] AS nvarchar(max))
FROM [Customers] AS [c]
INNER JOIN [Purchases] AS [p] ON [c].[Id] = [p].[CustomerId]

SelectMany LEFT JOIN

使用DefaultIfEmpty

var q1 = 
				from c in context.Customers
                from p in c.Purchases.DefaultIfEmpty()
                select new { c.Name, p.Description, Price = (decimal?)p.Price };
                
// SELECT [c].[Name], [p].[Description], CAST([p].[Price] AS decimal(18,2)) AS [Price]
FROM [Customers] AS [c]
LEFT JOIN [Purchases] AS [p] ON [c].[Id] = [p].[CustomerId]

上面的写法在本地查询的时候可能会报错，当p为null时，p.Description p.Price会产生空指针错误。需要改写兼容两种场景

  var q1 =
                from c in context.Customers
                from p in c.Purchases.DefaultIfEmpty()
                select new
                {
                    c.Name,
                    Description = p == null ? "" : p.Description,
                    Price = p == null ? (double?)null : p.Price
                };
                
// SELECT [c].[Name], CASE
    WHEN [p].[Id] IS NULL THEN N''
    ELSE [p].[Description]
END AS [Description], CAST([p].[Price] AS decimal(18,2)) AS [Price]
FROM [Customers] AS [c]
LEFT JOIN [Purchases] AS [p] ON [c].[Id] = [p].[CustomerId]

现在添加 Price>60 筛选

   var q1 =
                from c in context.Customers
                from p in c.Purchases.DefaultIfEmpty()
                where p.Price > 60
                select new
                {
                    c.Name,
                    Description = p == null ? "" : p.Description,
                    Price = p == null ? (double?)null : p.Price
                };
                
// SELECT [c].[Name], CASE
    WHEN [p].[Id] IS NULL THEN N''
    ELSE [p].[Description]
END AS [Description], [p].[Price]
FROM [Customers] AS [c]
LEFT JOIN [Purchases] AS [p] ON [c].[Id] = [p].[CustomerId]
WHERE [p].[Price] > 60.0E0

发现where是在left join之后，正确的写法应该是

 var q1 =
                from c in context.Customers
                from p in c.Purchases.Where(p => p.Price > 60).DefaultIfEmpty()
                select new
                {
                    c.Name,
                    Description = p == null ? "" : p.Description,
                    Price = p == null ? (double?)null : p.Price
                };
                
// SELECT [c].[Name], CASE
    WHEN [t].[Id] IS NULL THEN N''
    ELSE [t].[Description]
END AS [Description], [t].[Price]
FROM [Customers] AS [c]
LEFT JOIN (
    SELECT [p].[Id], [p].[CustomerId], [p].[Description], [p].[Price]
    FROM [Purchases] AS [p]
    WHERE [p].[Price] > 60.0E0
) AS [t] ON [c].[Id] = [t].[CustomerId]

Select子查询产生的是层次化的结构，不需要进行额外的null值处理。