LINQ demo: 联合查询，排序，分组查询

 LINQ基础：扩展方法，委托，Lambda表达式，IEnumrable类型

IEnumrable基础：
IEnumrator枚举器，可迭代类型.
枚举器IEnumrator和Enum枚举是完全不同的两个东西，要区分开.

我们定义一个数组对象，int[] nums = {1,2,3,4,5};
就可以用foreach对数组进行遍历/迭代，为什么数组可以迭代呢？
我们再定义一个字符串string str = "David";
试一下，同样可以用foreach进行迭代，为什么呢？
因为我们定义的数组类型是Array类的派生类，而Array类实现了IEnumrable接口.
同样，string类型也实现了IEnumrable接口.

所以，对于自定义类型，只要实现了IEnumrable接口，就可以对这个类型的对象进行遍历.

yield关键字，返回集合元素.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _9._7_LINQ
{
    class Program
    {
        
        static void Main(string[] args)
        {
            List<Person> people = new List<Person> {
                new Person { ID = 1, Name = "Lucy",MajorID = 100 },
                new Person { ID = 2, Name = "Jim",MajorID = 101 },
                new Person { ID = 3, Name = "Tom",MajorID = 103 },
                new Person { ID = 4, Name = "Green",MajorID = 102 },
                new Person { ID = 5, Name = "GouShengzi",MajorID = 101 },
                new Person { ID = 6, Name = "WangErmazi",MajorID = 101 },
                new Person { ID = 7, Name = "Lily",MajorID = 103 },
            };
            List<Major> majors = new List<Major>
            {
                new Major{MajorID = 100,MajorName = "English"},
                new Major{MajorID = 101,MajorName = "Electric"},
                new Major{MajorID = 102,MajorName = "France"},
                new Major{MajorID = 103,MajorName = "Math"},
            };

            //表达式查询法 orderby一般在查询条件where后面，descending指明是降序排列
            var list0 = from p in people
                        where p.ID > 1
                        orderby p.ID
                        descending//ascending
                        select p.ID;

            //扩展方法查询法
            //先查询出ID列返回可迭代集合，然后再筛选出满足条件的集合元素，然后排序，最后转成泛型列表
            List<int> res = people.Select(p => p.ID).Where(id =>id>1).OrderByDescending(i=>i).ToList();

            //先筛选出满足条件的源集合，再查询出ID列组成的可迭代集合，最后转成泛型列表
            List<int> res1 = people.Where(p => p.ID > 1).Select(p => p.ID).OrderByDescending(i => i).ToList();

            //联合查询(表达式) 直观的显示每个人的专业名称是什么
            var resIEnumrable = from p in people
                    from m in majors
                    where p.MajorID == m.MajorID
                    select new { p.ID,p.Name,m.MajorName };
            foreach (var item in resIEnumrable)
            {
                Console.WriteLine(item);
            }
            Console.ReadKey();

            //联合查询 - 扩展方法
            //public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult>(this IEnumerable<TSource> source, Func<TSource, IEnumerable<TCollection>> collectionSelector, Func<TSource, TCollection, TResult> resultSelector);
            //第一个参数是this，表明数据源S
            //第二个参数，输入参数为数据源的元素，返回另一个集合A，
            //第三个参数是Func委托占位符，委托的第一个参数是集合S的元素，集合A的元素，返回一个新的集合元素
            var newIEnumrable = people.SelectMany(p => majors, (p, m) =>new { p, m }).Where(item => item.p.MajorID == item.m.MajorID);
            foreach (var item in newIEnumrable)
            {
                Console.WriteLine($"{item.p.ID},{item.p.Name},{item.m.MajorName}");
            }
            Console.ReadKey();
            
            //join on联合查询
            var res_joinon = from p in people
                             join m in majors on p.MajorID equals m.MajorID
                             select new { p, m };
            foreach (var item in res_joinon)
            {
                Console.WriteLine($"{item.p.ID},{item.p.Name},{item.m.MajorName}");
            }
            Console.ReadKey();

            //join联合查询
            //public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector);
            //第一个参数this， 扩展方法的对象
            //第二个参数，另一个集合对象
            //第三个参数，设定另一个集合比较的字段
            //第四个参数，设定源集合的比较字段
            //返回第三第四个参数值匹配的集合
            var res_joinon2 = people.Join(majors, p => p.MajorID, m => m.MajorID, (p, m) => new { p.ID, p.Name, m.MajorName });
            foreach (var item in res_joinon2)
            {
                Console.WriteLine($"{item.ID},{item.Name},{item.MajorName}");
            }
            Console.ReadKey();

            //groupby分组r
            Console.WriteLine("grouyb by major name:");
            var subgroups = res_joinon2.GroupBy(a => a.MajorName);
            foreach (var sngSubgroup in subgroups)
            {
                foreach (var item in sngSubgroup)
                {
                    Console.WriteLine(item);
                }
                Console.WriteLine("\r\n");
            }
            Console.ReadKey();
        }
    }

    public class Person
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public byte MajorID { get; set; }
    }
    public class Major
    {
        public int MajorID { get; set; }
        public string MajorName { get; set; }
    }
}