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的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分为四组:
/* 遍历 输出 */
/*适用于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
,可以有效地组织和提取数据集中的关键信息,为数据分析和决策支持提供强大的数据支持。
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)