LINQ之SelectMany（from.. from..复合from子句）

[C#] LINQ之SelectMany - cnxy - 博客园 (cnblogs.com)

LINQ TO SQL中的selectMany - min.jiang - 博客园 (cnblogs.com)

Enumerable.SelectMany 方法 (System.Linq) | Microsoft Docs

from 子句 - C# 参考 | Microsoft Docs

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

代码如下

void Main()
{
    List<Person> personList = new List<Person>
            {
                new Person
                {
                    Name = "P1", Age = 18, Gender = "Male",
                    Dogs = new Dog[]
                    {
                        new Dog { Name = "D1" },
                        new Dog { Name = "D2" }
                    }
                },
                new Person
                {
                    Name = "P2", Age = 19, Gender = "Male",
                    Dogs = new Dog[]
                    {
                        new Dog { Name = "D3" }
                    }
                },
                new Person
                {
                    Name = "P3", Age = 17,Gender = "Female",
                    Dogs = new Dog[]
                    {
                        new Dog { Name = "D4" },
                        new Dog { Name = "D5" },
                        new Dog { Name = "D6" }
                    }
                }
            };

    Console.WriteLine("********第一种用法*************************************");
    //官方释义：将序列的每个元素投影到 IEnumerable<TResult> 并将结果序列合并为一个序列。
    //public static IEnumerable<TResult> SelectMany<TSource, TResult>(this IEnumerable<TSource> source, Func<TSource, IEnumerable<TResult>> selector);

    var results1 = personList.SelectMany(p => p.Dogs);
    //    其等价的LINQ语句为：  
    //var results1 = from p in personList
    //            from d in p.Dogs
    //            select d;
    foreach (var dog in results1)
    {
        Console.WriteLine(dog.Name);
    }
    Console.WriteLine();


    Console.WriteLine("********第二种用法*************************************");
    //官方释义：将序列的每个元素投影到 IEnumerable<TResult>，并将结果序列合并为一个序列。每个源元素的索引用于该元素的投影表。
    //public static IEnumerable<TResult> SelectMany<TSource, TResult>(this IEnumerable<TSource> source, Func<TSource, int, IEnumerable<TResult>> selector);

    var results2 = personList.SelectMany((p, i) => p.Dogs.Select(d => new
    {
        Name = $"{i},{d.Name}"
    }));

    foreach (var dog in results2)
    {
        Console.WriteLine(dog.Name);
    }
    Console.WriteLine();

    Console.WriteLine("********第三种用法*************************************");
    //官方释义：将序列的每个元素投影到 IEnumerable<TCollection>，并将结果序列合并为一个序列，并对其中每个元素调用结果选择器函数。
    //public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult>(this IEnumerable<TSource> source, Func<TSource, IEnumerable<TCollection>> collectionSelector, Func<TSource, TCollection, TResult> resultSelector);

    var results3 = personList.SelectMany(p => p.Dogs, (p, d) => new { PersonName = p.Name, DogName = d.Name });
    //    其等价的LINQ语句为：
    //var results3 = from p in personList
    //              from d in p.Dogs
    //              select new { PersonName = p.Name, DogName = d.Name };

    foreach (var result in results3)
    {
        Console.WriteLine($"{result.PersonName},{result.DogName}");
    }
    Console.WriteLine();

    Console.WriteLine("********第四种用法*************************************");
    //官方释义：将序列的每个元素投影到 IEnumerable<TCollection>，并将结果序列合并为一个序列，并对其中每个元素调用结果选择器函数。每个源元素的索引用于该元素的中间投影表。
    //public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult>(this IEnumerable<TSource> source, Func<TSource, int, IEnumerable<TCollection>> collectionSelector, Func<TSource, TCollection, TResult> resultSelector);
    var results4 = personList.SelectMany((p, i) => p.Dogs.Select(d => new
                                            {
                                                Name = $"{i},{d.Name}"
                                            })
                                        , (p, d) => new { PersonName = p.Name, DogName = d.Name });
    foreach (var result in results4)
    {
        Console.WriteLine($"{result.PersonName},{result.DogName}");
    }
    Console.WriteLine();
}

class Person
{
    public string Name { set; get; }
    public int Age { set; get; }
    public string Gender { set; get; }
    public Dog[] Dogs { set; get; }
}

public class Dog
{
    public string Name { set; get; }
}

   

结果：

********第一种用法*************************************
D1
D2
D3
D4
D5
D6

********第二种用法*************************************
0,D1
0,D2
1,D3
2,D4
2,D5
2,D6

********第三种用法*************************************
P1,D1
P1,D2
P2,D3
P3,D4
P3,D5
P3,D6

********第四种用法*************************************
P1,0,D1
P1,0,D2
P2,1,D3
P3,2,D4
P3,2,D5
P3,2,D6

复合from子句与join子句

复合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;