C# Linq查询

12.2.1 筛选

public static void Filtering()
        {
            var racers = from r in Formula1.GetChampions()
                         where r.Wins > 15 && (r.Country == "Brazil" || r.Country == "Austria")
                         select r;

            foreach (var r in racers)
            {
                Console.WriteLine($"{r:A}");
            }
        }

public static void FilteringWithMethods()
        {
            var racers = Formula1.GetChampions()
                .Where(r => r.Wins > 15 && (r.Country == "Brazil" || r.Country == "Austria"));

            foreach (var r in racers)
            {
                Console.WriteLine($"{r:A}");
            }
        }

12.2.2 用索引筛选

public static void FilteringWithIndex()
        {
            // 使用索引返回姓氏以A开头、索引为偶数的赛车手
            var racers = Formula1.GetChampions()
                .Where((r,index)=>r.LastName.StartsWith("A") && index % 2 !=0);
            foreach (var r in racers)
            {
                Console.WriteLine($"{r:A}");
            }
        }

12.2.3 类型筛选

public static void TypeFiltering()
        {
            object[] data = { "one", 2, 3, "four", "five", 6 };
            // 使用OfType()把string类传给泛型参数，就从集合中仅返回字符串
            var query = data.OfType<string>();
            foreach (var s in query)
            {
                Console.WriteLine(s);
            }
        }

12.2.4 复合的from子句

public static void CompoundFrom()
        {
            // 第一个from子句返回Racer对象；
            // 第二个from子句访问Racer类中的Cars属性，返回所有类型赛车；
            // 接着在where子句中使用这些赛车筛选驾驶法拉利的所有冠军；
            var ferrariDrivers = from r in Formula1.GetChampions()
                                 from c in r.Cars
                                 where c == "Ferrari"
                                 orderby r.LastName
                                 select $"{r.FirstName} {r.LastName}";

            foreach (var racer in ferrariDrivers)
            {
                Console.WriteLine(racer);
            }
        }

public static void CompoundFromWithMethods()
        {
            /* 
             * 第一个参数是隐式参数，它从GetChampions()方法中接受Racer对象序列；
             * 
             * 第二个参数是collectionSelector委托，其中定义了内部序列。
             * 在lambda表达式r =>r.Cars中，应返回赛车集合；
             * 
             * 第三个参数是一个委托，现在为每个赛车调用该委托，接收Racer和Car对象。
             * lambda表达式创建了一个匿名类型，它有Racer和Car属性。
             * 
             * SelectMany()方法的结果是摊平了赛车手和赛车的层次结构，
             * 为每辆赛车返回匿名类型的一个新对象集合。
             */

            var ferrariDrivers = Formula1.GetChampions()
                .SelectMany(r => r.Cars, (r, c) => new { Racer = r, Car = c })
                .Where(r => r.Car == "Ferrari")
                .OrderBy(r => r.Racer.LastName)
                .Select(r => r.Racer.FirstName + " " + r.Racer.LastName);
                //.SelectMany(r => r.Cars, (r1, cars) => new { Racer1 = r1, Cars1 = cars })
                //.Where(item => item.Cars1.Contains("Ferrari"))
                //.OrderBy(item => item.Racer1.LastName)
                //.Select(item => $"{item.Racer1.FirstName} {item.Racer1.LastName}");

            foreach (var racer in ferrariDrivers)
            {
                Console.WriteLine(racer);
            }
        }

12.2.5 排序

public static void SortDescending()
        {
            Console.WriteLine("Show all champions from Brazil ordered by wins descending");
            Console.WriteLine();

            var racers = from r in Formula1.GetChampions()
                         where r.Country == "Brazil"
                         orderby r.Wins descending
                         select r;

            foreach (var r in racers)
            {
                Console.WriteLine($"{r:A}");
            }
        }

public static void SortDescendingWithMethods()
        {
            Console.WriteLine("Show all champions from Brazil ordered by wins descending");
            Console.WriteLine();
            var racers = Formula1.GetChampions()
                .Where(r => r.Country == "Brazil")
                .OrderByDescending(r => r.Wins);

            foreach (var r in racers)
            {
                Console.WriteLine($"{r:A}");
            }
        }

        // 多次排序
        public static void SortMultiple()
        {
            Console.WriteLine("Show the first 10 champions ordered by country, lastname, firstname");
            Console.WriteLine();

            var racers = (from r in Formula1.GetChampions()
                          orderby r.Country, r.LastName, r.FirstName
                          select r).Take(10);

            foreach (var racer in racers)
            {
                Console.WriteLine($"{racer.Country}: {racer.LastName}, {racer.FirstName}");
            }
        }

        public static void SortMultipleWithMethods()
        {
            // 先按国家排序，再按照姓氏排序，最后按照名字排序。
            // Take()用于返回前10个
            var racers = Formula1.GetChampions()
                            .OrderBy(r => r.Country)
                            .ThenBy(r => r.LastName)
                            .ThenBy(r => r.FirstName)
                            .Take(10);

            foreach (var racer in racers)
            {
                Console.WriteLine($"{racer.Country}: {racer.LastName}, {racer.FirstName}");
            }
        }

12.2.6 分组

public static void Grouping()
        {
            /*
             * 子句group r by r.Country into g根据Country属性组合所有的赛车手
             * 并定义一个新的标识符g，它以后用于访问分组的结果信息。
             * 
             * group子句的结果根据应用到的分组结果上的扩展方法Count()来排序，
             * 如果冠军数相同，就根据关键字来排序，该关键字分组所用的关键字国家。
             * 
             * where子句根据至少有两项的分组来筛选结果，
             * select子句创建一个带Country和Count属性的匿名类型
             */
             
            var countries =
                from r in Formula1.GetChampions()
                group r by r.Country into g
                orderby g.Count() descending, g.Key
                where g.Count() > 2
                select new
                {
                    Country = g.Key,
                    Count = g.Count()
                };

            foreach (var item in countries)
            {
                Console.WriteLine($"{item.Country,-10} {item.Count}");
            }

        }

        public static void GroupingWithMethods()
        {
            var countries = Formula1.GetChampions()
              .GroupBy(r => r.Country)
              .OrderByDescending(g => g.Count())
              .ThenBy(g => g.Key)
              .Where(g => g.Count() >= 2)
              .Select(g => new
              {
                  Country = g.Key,
                  Count = g.Count()
              });


            foreach (var item in countries)
            {
                Console.WriteLine($"{item.Country,-10} {item.Count}");
            }

        }

12.2.7 Linq查询中的变量

public static void GroupingWithVariables()
        {
           /*
            * 在上面编写的Linq查询中，Count方法调用了多次。
            * 使用let子句可以改变这种方式。
            * let允许在linq查询中定义变量
            */

            var countries = from r in Formula1.GetChampions()
                            group r by r.Country into g
                            let count = g.Count()
                            orderby count descending, g.Key
                            where count >= 2
                            select new
                            {
                                Country = g.Key,
                                Count = count
                            };

            foreach (var item in countries)
            {
                Console.WriteLine($"{item.Country,-10} {item.Count}");
            }

        }

        public static void GroupingWithAnonymousTypes()
        {
            /*
             * 为了定义传递给下一个方法的额外数据，可以使用Select方法来创建匿名类型。
             * 这里创建了一个带Group和Count属性的匿名类型。
             * 带有这些属性的一组项传递给OrderByDescending方法，基于匿名类型的Count属性排序。
             */
            var countries = Formula1.GetChampions()
              .GroupBy(r => r.Country)
              .Select(g => new { Group = g, Count = g.Count() })
              .OrderByDescending(g => g.Count)
              .ThenBy(g => g.Group.Key)
              .Where(g => g.Count >= 2)
              .Select(g => new
              {
                  Country = g.Group.Key,
                  g.Count
              });

            foreach (var item in countries)
            {
                Console.WriteLine($"{item.Country,-10} {item.Count}");
            }
        }

12.2.8 对嵌套的对象分组

public static void GroupingAndNestedObjects()
        {
            var countries = from r in Formula1.GetChampions()
                            group r by r.Country into g
                            let count = g.Count()
                            orderby count descending, g.Key
                            where count >= 2
                            select new
                            {
                                Country = g.Key,
                                Count = count,
                                Racers = from r1 in g
                                         orderby r1.LastName
                                         select r1.FirstName + " " + r1.LastName
                            };
            foreach (var item in countries)
            {
                Console.WriteLine($"{item.Country,-10} {item.Count}");
                foreach (var name in item.Racers)
                {
                    Console.Write($"{name}; ");
                }
                Console.WriteLine();
            }
        }

        public static void GroupingAndNestedObjectsWithMethods()
        {
            var countries = Formula1.GetChampions()
                .GroupBy(r => r.Country)
                .Select(g => new
                {
                    Group = g,
                    g.Key,
                    Count = g.Count()
                })
                .OrderByDescending(g => g.Count)
                .ThenBy(g => g.Key)
                .Where(g => g.Count >= 2)
                .Select(g => new
                {
                    Country = g.Key,
                    g.Count,
                    Racers = g.Group.OrderBy(r => r.LastName).Select(r => r.FirstName + " " + r.LastName)
                });

            foreach (var item in countries)
            {
                Console.WriteLine($"{item.Country,-10} {item.Count}");
                foreach (var name in item.Racers)
                {
                    Console.Write($"{name}; ");
                }
                Console.WriteLine();
            }
        }

12.2.9 内连接

public static void InnerJoin()
        {
            // 查询赛车手
            var racers = from r in Formula1.GetChampions()
                         from y in r.Years
                         select new
                         {
                             Year = y,
                             Name = r.FirstName + " " + r.LastName
                         };
            // 查询车队
            var teams = from t in Formula1.GetConstructorChampions()
                        from y in t.Years
                        select new
                        {
                            Year = y,
                            t.Name
                        };
            // 再通过join子句，根据赛车手获得冠军的年份和车队获得冠军的年份进行连接
            var racersAndTeams =
                  (from r in racers
                   join t in teams on r.Year equals t.Year
                   orderby t.Year
                   select new
                   {
                       r.Year,
                       Champion = r.Name,
                       Constructor = t.Name
                   }).Take(10);

            Console.WriteLine("Year  World Champion\t   Constructor Title");
            foreach (var item in racersAndTeams)
            {
                Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}");
            }
        }

        public static void InnerJoinWithMethods()
        {
            /*
             * 调用Join方法，通过第一个参数传递车队，把他们与赛车手连接起来，
             * 指定外部和内部集合的关键字选择器，并通过最后一个参数定义结果选择器。
             */
            var racers = Formula1.GetChampions()
                .SelectMany(r => r.Years, (r1, year) =>
                new
                {
                    Year = year,
                    Name = $"{r1.FirstName} {r1.LastName}"
                });

            var teams = Formula1.GetConstructorChampions()
                .SelectMany(t => t.Years, (t, year) =>
                new
                {
                    Year = year,
                    t.Name
                });

            var racersAndTeams = racers.Join(
                teams,
                r => r.Year,
                t => t.Year,
                (r, t) =>
                    new
                    {
                        r.Year,
                        Champion = r.Name,
                        Constructor = t.Name
                    }).OrderBy(item => item.Year).Take(10);

            Console.WriteLine("Year  World Champion\t   Constructor Title");
            foreach (var item in racersAndTeams)
            {
                Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}");
            }
        }

12.2.10 左外连接

public static void LeftOuterJoin()
        {
            var racers = from r in Formula1.GetChampions()
                         from y in r.Years
                         select new
                         {
                             Year = y,
                             Name = r.FirstName + " " + r.LastName
                         };

            var teams = from t in Formula1.GetConstructorChampions()
                        from y in t.Years
                        select new
                        {
                            Year = y,
                            t.Name
                        };
            /*
             * 左外连接用join子句和DefaultIfEmpty的方法定义。
             * 如果查询的左侧(赛车手)没有匹配的车队冠军，
             * 就使用DefaultIfEmpty方法定义其右侧默认值。
             */
            var racersAndTeams =
              (from r in racers
               join t in teams on r.Year equals t.Year into rt
               from t in rt.DefaultIfEmpty()
               orderby r.Year
               select new
               {
                   r.Year,
                   Champion = r.Name,
                   Constructor = t == null ? "no constructor championship" : t.Name
               }).Take(10);

            Console.WriteLine("Year  Champion\t\t   Constructor Title");
            foreach (var item in racersAndTeams)
            {
                Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}");
            }
        }

        public static void LeftOuterJoinWithMethods()
        {
            var racers = Formula1.GetChampions()
                .SelectMany(r => r.Years, (r1, year) =>
                new
                {
                    Year = year,
                    Name = $"{r1.FirstName} {r1.LastName}"
                });

            var teams = Formula1.GetConstructorChampions()
                .SelectMany(t => t.Years, (t, year) =>
                new
                {
                    Year = year,
                    Name = t.Name
                });

            var racersAndTeams =
                racers.GroupJoin(
                    teams,
                    r => r.Year,
                    t => t.Year,
                    (r, ts) => new
                    {
                        Year = r.Year,
                        Champion = r.Name,
                        Constructors = ts
                    })
                    .SelectMany(
                        item => item.Constructors.DefaultIfEmpty(),
                        (r, t) => new
                        {
                            Year = r.Year,
                            Champion = r.Champion,
                            Constructor = t?.Name ?? "no constructor championship"
                        });

            Console.WriteLine("Year  Champion\t\t   Constructor Title");
            foreach (var item in racersAndTeams)
            {
                Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}");
            }
        }

12.2.11 组连接

public static void GroupJoin()
        {
            var racers = from cs in Formula1.GetChampionships()
                         from r in new List<(int Year, int Position, string FirstName, string LastName)>()
                         {
                             (cs.Year, Position: 1, FirstName: cs.First.FirstName(), LastName: cs.First.LastName()),
                             (cs.Year, Position: 2, FirstName: cs.Second.FirstName(), LastName: cs.Second.LastName()),
                             (cs.Year, Position: 3, FirstName: cs.Third.FirstName(), LastName: cs.Third.LastName())
                         }
                         select r;

            var q = (from r in Formula1.GetChampions()
                     join r2 in racers on
                     (
                         r.FirstName,
                         r.LastName
                     )
                     equals
                     (
                         r2.FirstName,
                         r2.LastName
                     )
                     into yearResults
                     select
                     (
                         r.FirstName,
                         r.LastName,
                         r.Wins,
                         r.Starts,
                         Results: yearResults
                     ));

            foreach (var r in q)
            {
                Console.WriteLine($"{r.FirstName} {r.LastName}");
                foreach (var results in r.Results)
                {
                    Console.WriteLine($"\t{results.Year} {results.Position}");
                }
            }
        }

        public static void GroupJoinWithMethods()
        {
            var racers = Formula1.GetChampionships()
              .SelectMany(cs => new List<(int Year, int Position, string FirstName, string LastName)>
              {
                 (cs.Year, Position: 1, FirstName: cs.First.FirstName(), LastName: cs.First.LastName()),
                 (cs.Year, Position: 2, FirstName: cs.Second.FirstName(), LastName: cs.Second.LastName()),
                 (cs.Year, Position: 3, FirstName: cs.Third.FirstName(), LastName: cs.Third.LastName())
              });

            var q = Formula1.GetChampions()
                .GroupJoin(racers,
                    r1 => (r1.FirstName, r1.LastName),
                    r2 => (r2.FirstName, r2.LastName),
                    (r1, r2s) => (r1.FirstName, r1.LastName, r1.Wins, r1.Starts, Results: r2s));


            foreach (var r in q)
            {
                Console.WriteLine($"{r.FirstName} {r.LastName}");
                foreach (var results in r.Results)
                {
                    Console.WriteLine($"{results.Year} {results.Position}");
                }
            }
        }

12.2.12 集合操作

// Distinct()、Union()、Intersect()、Except()都是集合操作。
        public static void SetOperations()
        {
            IEnumerable<Racer> racersByCar(string car) =>
                from r in Formula1.GetChampions()
                from c in r.Cars
                where c == car
                orderby r.LastName
                select r;

            Console.WriteLine("World champion with Ferrari and McLaren");
            foreach (var racer in racersByCar("Ferrari").Intersect(racersByCar("McLaren")))
            {
                Console.WriteLine(racer);
            }
        }

public static void Except()
        {
            var racers = Formula1.GetChampionships().SelectMany(cs => new List<RacerInfo>()
               {
                 new RacerInfo {
                   Year = cs.Year,
                   Position = 1,
                   FirstName = cs.First.FirstName(),
                   LastName = cs.First.LastName()
                 },
                 new RacerInfo {
                   Year = cs.Year,
                   Position = 2,
                   FirstName = cs.Second.FirstName(),
                   LastName = cs.Second.LastName()
                 },
                 new RacerInfo {
                   Year = cs.Year,
                   Position = 3,
                   FirstName = cs.Third.FirstName(),
                   LastName = cs.Third.LastName()
                 }
               });

            var nonChampions = racers.Select(r =>
              new
              {
                  r.FirstName,
                  r.LastName
              }).Except(Formula1.GetChampions().Select(r =>
                new
                {
                    r.FirstName,
                    r.LastName
                }));

            foreach (var r in nonChampions)
            {
                Console.WriteLine($"{r.FirstName} {r.LastName}");
            }
        } 

12.2.13 合并

public static void ZipOperation()
        {
            var racerNames = from r in Formula1.GetChampions()
                             where r.Country == "Italy"
                             orderby r.Wins descending
                             select new
                             {
                                 Name = r.FirstName + " " + r.LastName
                             };

            var racerNamesAndStarts = from r in Formula1.GetChampions()
                                      where r.Country == "Italy"
                                      orderby r.Wins descending
                                      select new
                                      {
                                          r.LastName,
                                          r.Starts
                                      };
            /*
             * Zip()对于合并，第一个集合中的第一项和第二个集合中的第一项合并，
             * 第一个集合中第二项会与第二个集合中的第二项合并，以此内推。
             * 如果两个序列的项数不同，就在到达较小集合的末尾时停止。
             * 
             * 通过参数first接收第一个集合的元素，通过参数second接收第二个集合的元素。
             */
            var racers = racerNames.Zip(racerNamesAndStarts, (first, second) => first.Name + ", starts: " + second.Starts);
            foreach (var r in racers)
            {
                Console.WriteLine(r);
            }
        }

12.2.14 分区

public static void Partitioning()
        {
            int pageSize = 5;

            int numberPages = (int)Math.Ceiling(Formula1.GetChampions().Count() /
                  (double)pageSize);

            for (int page = 0; page < numberPages; page++)
            {
                Console.WriteLine($"Page {page}");
                /*
                 * 把扩展方法Skip()和Take()添加到查询的最后。
                 * Skip()方法先忽略根据页面大小和实际页数计算出的项数，
                 * 在使用Take()方法根据页面大小提取一定数量的项。
                 * 
                 * 使用TakeWhile()和SkipWhile()扩展方法，还可以传递一个谓词，
                 * 根据结果提取或跳过某些项。
                 */ 
                var racers =
                   (from r in Formula1.GetChampions()
                    orderby r.LastName, r.FirstName
                    select r.FirstName + " " + r.LastName).
                   Skip(page * pageSize).Take(pageSize);

                foreach (var name in racers)
                {
                    Console.WriteLine(name);
                }
                Console.WriteLine();
            }
        }

12.2.15 聚合操作符

public static void AggregateCount()
        {
            // Count()方法来筛选，只返回获得冠军次数超过3次的赛车手。
            var query = from r in Formula1.GetChampions()
                        let numberYears = r.Years.Count()
                        where numberYears >= 3
                        orderby numberYears descending, r.LastName
                        select new
                        {
                            Name = r.FirstName + " " + r.LastName,
                            TimesChampion = numberYears
                        };

            foreach (var r in query)
            {
                Console.WriteLine($"{r.Name} {r.TimesChampion}");
            }
        }
        public static void AggregateSum()
        {
            /*
             * Sum()方法汇总序列中的所有数字，返回这些数字的和。
             * 
             * 首先根据国家对赛车手分组，再在新创建的匿名类型中，
             * 把Wins属性赋予某个国家赢得比赛的总次数。
             */ 
            var countries = (from c in
                                 from r in Formula1.GetChampions()
                                 group r by r.Country into c
                                 select new
                                 {
                                     Country = c.Key,
                                     Wins = (from r1 in c
                                             select r1.Wins).Sum()
                                 }
                             orderby c.Wins descending, c.Country
                             select c).Take(5);

            foreach (var country in countries)
            {
                Console.WriteLine($"{country.Country} {country.Wins}");
            }
        }

12.2.16 转换操作符

public static void ToList()
        {
            /*
             * 查询可以推迟到访问数据项时再执行。在迭代中使用查询时，查询会执行。
             * 而使用转换操作符会立即执行查询，把查询结果放在数组、列表或字典中。
             * 
             * 下面调用ToList()扩展方法，立即执行查询，结果放到List<T> 类中。
             */ 
            List<Racer> racers = (from r in Formula1.GetChampions()
                                  where r.Starts > 200
                                  orderby r.Starts descending
                                  select r).ToList();

            foreach (var racer in racers)
            {
                Console.WriteLine($"{racer} {racer:S}");
            }
        }

        /*
         * 注意：Dictionary<TKey,TValue>类只支持一个键对应一个值。
         * 在Lookup<TKey,TValue>类中，一个键可以对应多个值。
         */
        public static void ToLookup()
        {
            /*
             * 摊平赛车手和赛车序列，创建带有Car和Racer属性的匿名类型。
             * 在返回Lookup对象中，键的类型应是表示汽车的string，值类型应是Racer。
             * 为了进行这个选择，可以给ToLookup()的一个重载版本传递一个键和一个元素选择器。
             * 键选择器引用Car属性，元素选择器引用Racer属性。
             */ 
            var racers = (from r in Formula1.GetChampions()
                          from c in r.Cars
                          select new
                          {
                              Car = c,
                              Racer = r
                          }).ToLookup(cr => cr.Car, cr => cr.Racer);

            if (racers.Contains("Williams"))
            {
                foreach (var williamsRacer in racers["Williams"])
                {
                    Console.WriteLine(williamsRacer);
                }
            }
        }

        public static void ConvertWithCast()
        {
            var list = new System.Collections.ArrayList(Formula1.GetChampions() as System.Collections.ICollection);
            /*
             * 需要在非类型化的集合上使用Linq查询，就可以使用Cast();
             * 
             * 下面基于Object类型的ArrayList集合用Racer对象填充。
             * 为定义强类型化的查询，可使用Cast()方法。
             */
            var query = from r in list.Cast<Racer>()
                        where r.Country == "USA"
                        orderby r.Wins descending
                        select r;
            foreach (var racer in query)
            {
                Console.WriteLine($"{racer:A}");
            }
        }

12.2.17 生成操作符

public static void GenerateRange()
        {
            /*
             * 生成操作符Range()、Empty()、Repeat()不是扩展方法，
             * 而是返回序列的正常静态方法。
             * 在Linq to Objects中，这些可用于Enumerable类。
             *
             * 有时需要填充一个范围数字，此时就应使用Range()。
             * 把第一个参数作为起始值，把第二个参数作为要填充的项数。
             */

            var values = Enumerable.Range(1, 20);
            foreach (var item in values)
            {
                Console.Write($"{item} ", item);
            }
            Console.WriteLine();

            /*
             * 可以把该结果与其他扩展方法合并起来，获得另一个结果，例
             * var values = Enumerable.Range(1,20).Select(n=>n*3);
             * 
             * ··········
             * Empty()方法返回一个不返回值得迭代器，它可以用于需要一个集合的参数，
             * 其中可以给参数传递空集合。
             * 
             * Repeat()方法返回一个迭代器，该迭代器把同一个值重复特定的次数。
             */
        }