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

举报
Damon小智 发表于 2024/04/30 19:08:07 2024/04/30
【摘要】 本篇文章我们将演示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数据源:

image.png


二、LINQ的GroupBy语法示例

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分为四组:

image.png

/* 遍历 输出 */
/*适用于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),
                  };

image.png

/* 遍历 输出 */
/*适用于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,可以有效地组织和提取数据集中的关键信息,为数据分析和决策支持提供强大的数据支持。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。