C#进阶-LINQ表达式之GroupBy分组查询

本篇文章我们将演示LINQ扩展包基础语法里的GroupBy分组查询，并实现投影等实际操作中常用的类型转换手法。目前LINQ支持两种语法，我会在每个案例前先用大家熟知的SQL语句表达，再在后面用C#的两种LINQ语法分别实现。LINQ语法第一次接触难免感到陌生，最好的学习方式就是在项目中多去使用，相信会有很多感悟。

---

一、LINQ表达式学前准备

在学习之前，我们要做一些准备工作，我们需要创建User对象和包含User对象的集合，作为后面查询和输出的数据源。

1、C#代码准备

C#类：

class User
{
    public int id { get; set; } 
    public string name { get; set; } 
    public bool gender { get; set; }//male: true; female: fasle
    public int age { get; set; }
    public string occupation { get; set; } //职业
}

List<User> list = new List<User>()
{
    new User { id = 1, name = "Zhang Long", age = 38, gender = true, occupation = "Teacher"},
    new User { id = 2, name = "Zhang Jin", age = 18, gender = false, occupation = "Student"},
    new User { id = 3, name = "Zhang Shuai", age = 38, gender = false, occupation = "Teacher"},
    new User { id = 4, name = "Liu Guangzhi", age = 38, gender = false, occupation = "Doctor"},
    new User { id = 5, name = "Liu Ziming", age = 38, gender = true, occupation = "Doctor"},
    new User { id = 6, name = "Liu Shuai", age = 29, gender = false, occupation = "Doctor"},
    new User { id = 7, name = "Liu Jin", age = 21, gender = true, occupation = "Builder"},
    new User { id = 8, name = "Jiang Long", age = 38, gender = true, occupation = "Builder"},
    new User { id = 9, name = "Hu Ziming", age = 21, gender = true, occupation = "Student"},
    new User { id = 10, name = "Hu Jin", age = 21, gender = false, occupation = "Student"}
};

2、数据库准备

SQL数据源：

---

二、LINQ的多表连接语法示例

1、单属性分组查询全部信息

这里我们举一个最简单的例子，根据职业分组，获得每组的集合：

/* C#版本1 */
IEnumerable<IGrouping<string, User>> UserGroupByOccupation = list.GroupBy(s => s.occupation);
/* C#版本2 */
IEnumerable<IGrouping<string, User>> UserGroupByOccupation
                = from u in list
                   group u by u.occupation into n
                   select n;
/* C#版本3 */
//这里的版本3是版本2的衍生版本，用自定义对象类ListGroupResult替代 IGrouping<string, User>
class ListGroupResult
{
  public string Key { get; set; }
  public List<User> UserList { get; set; }
}
IIEnumerable<ListGroupResult> UserGroupByOccupation
                = from u in list
                  group u by u.occupation into n
                  select new ListGroupResult()
                  {
                      Key = n.Key,  //这个Key是occupation
                      UserList = n.ToList()
                  };

根据Occupation分为四组：

/* 遍历 输出 */
/*适用于C#版本1 和2*/
foreach(IGrouping<string, User> u in UserGroupByOccupation)
{
  Console.WriteLine(u.Key);
  foreach (User user in u)
  {
    Console.WriteLine(PrintUserObject(user));
  }
}
/*适用于C#版本3*/
foreach(ListGroupResult u in UserGroupByOccupation)
{
  Console.WriteLine(u.Key);
  foreach (User user in u.UserList)
  {
    Console.WriteLine(PrintUserObject(user));
  }
}
 /* 输出结果 */
Teacher
{id = 1, name = Zhang Long, age = 38, gender = True, occupation = Teacher}
{id = 3, name = Zhang Shuai, age = 38, gender = False, occupation = Teacher}
Student
{id = 2, name = Zhang Jin, age = 18, gender = False, occupation = Student}
{id = 9, name = Hu Ziming, age = 21, gender = True, occupation = Student}
{id = 10, name = Hu Jin, age = 21, gender = False, occupation = Student}
Doctor
{id = 4, name = Liu Guangzhi, age = 38, gender = False, occupation = Doctor}
{id = 5, name = Liu Ziming, age = 38, gender = True, occupation = Doctor}
{id = 6, name = Liu Shuai, age = 29, gender = False, occupation = Doctor}
Builder
{id = 7, name = Liu Jin, age = 21, gender = True, occupation = Builder}
{id = 8, name = Jiang Long, age = 38, gender = True, occupation = Builder}

---

2、多属性分组查询全部信息

这次，我们根据职业和性别两个属性分组，获得每组的集合：

/* C#版本1 */
class ListMultiGroupResult
{
  public string Occupation { get; set; }
  public bool Gender { get; set; }
  public List<User> UserList { get; set; }
}
IEnumerable<ListMultiGroupResult> UserGroupByOccupationAndGender 
                = list.GroupBy(s => new { s.occupation, s.gender })
                .Select(g => new ListMultiGroupResult()
                {
                    Occupation = g.Key.occupation,
                    Gender = g.Key.gender,
                    UserList = g.ToList()
                });

这里根据Occupation和Gender分组后一共分为七组：

/* 遍历 输出 */
/*适用于C#版本1*/
foreach(ListMultiGroupResult u in UserGroupByOccupationAndGender)
{
  Console.WriteLine(u.Occupation + "/" + u.Gender);
  foreach (User user in u.UserList)
  {
    Console.WriteLine(PrintUserObject(user));
  }
}
 /* 输出结果 */
Teacher/True
{id = 1, name = Zhang Long, age = 38, gender = True, occupation = Teacher}
Student/False
{id = 2, name = Zhang Jin, age = 18, gender = False, occupation = Student}
{id = 10, name = Hu Jin, age = 21, gender = False, occupation = Student}
Teacher/False
{id = 3, name = Zhang Shuai, age = 38, gender = False, occupation = Teacher}
Doctor/False
{id = 4, name = Liu Guangzhi, age = 38, gender = False, occupation = Doctor}
{id = 6, name = Liu Shuai, age = 29, gender = False, occupation = Doctor}
Doctor/True
{id = 5, name = Liu Ziming, age = 38, gender = True, occupation = Doctor}
Builder/True
{id = 7, name = Liu Jin, age = 21, gender = True, occupation = Builder}
{id = 8, name = Jiang Long, age = 38, gender = True, occupation = Builder}
Student/True
{id = 9, name = Hu Ziming, age = 21, gender = True, occupation = Student}

---

3、多属性分组查询全部信息

来看一个SQL中常用的场景例子：

/* SQL里的表达: 按照用户职业分组，查出每个分组的人数及各组的年龄最大值、最小值、平均值和总和 */
SELECT occupation,COUNT(id),MAX(age),MIN(age),AVG(age),SUM(age) FROM USER GROUP BY occupation;

/* C#版本1 */
class AgeGroupResult
{
  public string Key { get; set; }
  public int MaxAge { get; set; }
  public int MinAge { get; set; }
  public double AvgAge { get; set; }
  public int SumAge { get; set; }
}
IEnumerable<AgeGroupResult> userList
                = from u in list
                  group u by u.occupation into n
                  select new AgeGroupResult()
                  {
                      Key = n.Key,  //这个Key是occupation
                      MaxAge = n.Max(r => r.age),
                      MinAge = n.Min(r => r.age),
                      AvgAge = n.Average(r => r.age),
                      SumAge = n.Sum(r => r.age),
                  };

/* 遍历 输出 */
/*适用于C#版本1*/
foreach (AgeGroupResult u in userList)
{
  Console.WriteLine(PrintAgeGroupObject(u));
}

 /* 输出结果 */
{Key = Teacher, MaxAge = 38, MinAge = 38, AvgAge = 38, SumAge = 76}
{Key = Student, MaxAge = 21, MinAge = 18, AvgAge = 20, SumAge = 60}
{Key = Doctor, MaxAge = 38, MinAge = 29, AvgAge = 35, SumAge = 105}
{Key = Builder, MaxAge = 38, MinAge = 21, AvgAge = 29.5, SumAge = 59}

---

三、LINQ表达式GroupBy总结

在LINQ中，GroupBy 方法是一个非常有用的工具，它允许开发者根据某个属性或条件将数据源中的元素分组。这种分组操作对于数据聚合、统计或复杂的查询尤为重要。GroupBy 在处理数据库查询、内存中的集合等场合中广泛应用，它提供了一个灵活而强大的方式来组织数据和提取信息。

GroupBy 的使用场景：

• 数据报告：生成分组统计数据，如销售报告中的年度或地区销售分析。
• 数据归类：将数据根据特定标准归入不同类别，便于后续处理或展示。
• 复杂查询优化：通过对数据进行分组，可以在内存中更高效地处理大量数据，尤其是在从数据库检索数据前进行初步分组。

GroupBy 是LINQ中一个极具表达力的工具，它不仅可以简化复杂的数据处理任务，还可以提升代码的可读性和维护性。正确使用GroupBy，可以有效地组织和提取数据集中的关键信息，为数据分析和决策支持提供强大的数据支持。