C#进阶-LINQ表达式之多表查询(基础篇)

原创

Damon小智

修改于 2024-04-30 19:44:26

2990

修改于 2024-04-30 19:44:26

文章被收录于专栏：C#全栈文档库

本篇文章我们将演示LINQ扩展包基础语法里的多表查询，包括交集、并集、差集、去重、合并等实际操作中常用的类型转换手法。目前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、数据库准备

数据源1：

数据源2：

二、LINQ的GroupBy语法示例

1、交集 Intersect

如同数学中中的交集，集合1,2,3和集合2,3,4的交集是2,3，Linq的交集是两种相同结果类型结果集的重合部分。

比如下面这个例子：

/* SQL里的表达: 求25岁以上且薪水超过17000的用户姓名、职业*/
SELECT name,occupation FROM User WHERE age > 25; /*先查询25岁以上的用户姓名、职业*/
INTERSECT 
SELECT name,occupation FROM Salary WHERE salary > 17000; /*再查询薪水超过17000的用户姓名、职业*/

/* 在比较两个对象元素之前，我们先重写一个比较对象*/
class CompareUser : IEqualityComparer<User>
{
  public bool Equals(User x, User y)
  {
    if (x.name == y.name && x.occupation.ToLower() == y.occupation.ToLower())
      return true;
    return false;
  }
  public int GetHashCode(User obj)
  {
    return (obj.name+obj.occupation).Length;
  }
}
/*查询年龄大于25的用户集合，投影存储他们的姓名和职业*/
List<User> user_list = list.Where(u => u.age > 25)
                .Select(g => new User(){
                    name = g.name,
                    occupation = g.occupation
                }).ToList();
/*查询薪水大于17000的用户集合，投影存储他们的姓名和职业*/
List<User> salary_list = salaryList.Where(u => u.salary > 17000)
                .Select(g => new User(){
                    name = g.name,
                    occupation = g.occupation
                }).ToList();
/*取上面两个集合的交集为结果集*/
List<User> result_list = user_list.Intersect(salary_list, new CompareUser()).ToList();

/* 遍历 输出 */
foreach (User user in user_list)
{
  Console.WriteLine(PrintUserObject(user));
}
foreach (User user in salary_list)
{
  Console.WriteLine(PrintUserObject(user));
}
foreach (User user in result_list)
{
  Console.WriteLine(PrintUserObject(user));
}
 /* 输出结果 */
 /* 年龄大于25的用户集合 */
{id = 0, name = Zhang Long, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Zhang Shuai, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Liu Guangzhi, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Liu Ziming, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Liu Shuai, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Jiang Long, age = 0, gender = False, occupation = Builder}
 /* 薪水高于17000的用户集合 */
{id = 0, name = Liu Shuai, age = 0, gender = False, occupation = Doctor}
 /* 交集结果集 */
{id = 0, name = Liu Shuai, age = 0, gender = False, occupation = Doctor}

2、并集 Union

如同数学中中的并集，集合1,2,3和集合2,3,4的交集是1,2,3,4，Linq的并集是两种相同结果类型结果集的合并集合。

比如下面这个例子：

/* SQL里的表达: 求25岁以上和薪水少于8000的用户姓名、职业*/
SELECT name,occupation FROM User WHERE age > 25; /*先查询25岁以上的用户姓名、职业*/
UNION
SELECT name,occupation FROM Salary WHERE salary < 8000; /*再查询薪水少于8000的用户姓名、职业*/

/*同样需要上面求交集时的比较对象*/
/*查询年龄大于25的用户集合，投影存储他们的姓名和职业*/
List<User> user_list = list.Where(u => u.age > 25)
                .Select(g => new User(){
                    name = g.name,
                    occupation = g.occupation
                }).ToList();
/*查询薪水少于8000的用户集合，投影存储他们的姓名和职业*/
List<User> salary_list = salaryList.Where(u => u.salary < 8000)
                .Select(g => new User(){
                    name = g.name,
                    occupation = g.occupation
                }).ToList();
/*取上面两个集合的交集为结果集*/
List<User> result_list = user_list.Union(salary_list, new CompareUser()).ToList();

/* 遍历 输出 */
foreach (User user in user_list)
{
  Console.WriteLine(PrintUserObject(user));
}
foreach (User user in salary_list)
{
  Console.WriteLine(PrintUserObject(user));
}
foreach (User user in result_list)
{
  Console.WriteLine(PrintUserObject(user));
}
 /* 输出结果 */
 /* 年龄大于25的用户集合 */
{id = 0, name = Zhang Long, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Zhang Shuai, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Liu Guangzhi, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Liu Ziming, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Liu Shuai, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Jiang Long, age = 0, gender = False, occupation = Builder}
 /* 薪水少于8000的用户集合 */
{id = 0, name = Zhang Long, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Zhang Jin, age = 0, gender = False, occupation = Student}
{id = 0, name = Liu Jin, age = 0, gender = False, occupation = Builder}
{id = 0, name = Hu Ziming, age = 0, gender = False, occupation = Student}
{id = 0, name = Hu Jin, age = 0, gender = False, occupation = Student}
 /* 并集结果集 */
{id = 0, name = Zhang Long, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Zhang Shuai, age = 0, gender = False, occupation = Teacher}
{id = 0, name = Liu Guangzhi, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Liu Ziming, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Liu Shuai, age = 0, gender = False, occupation = Doctor}
{id = 0, name = Jiang Long, age = 0, gender = False, occupation = Builder}
{id = 0, name = Zhang Jin, age = 0, gender = False, occupation = Student}
{id = 0, name = Liu Jin, age = 0, gender = False, occupation = Builder}
{id = 0, name = Hu Ziming, age = 0, gender = False, occupation = Student}
{id = 0, name = Hu Jin, age = 0, gender = False, occupation = Student}

3、全集 Concat

不同于并集(Union)去除了两个集合重复的元素，Concat保留重复的元素。

比如，{1,2,3}和{3,4,5}的Union结果是{1,2,3,4,5}，而Concat的结果是{1,2,3,3,4,5}。

4、差集 Except

如同数学中中的差集，集合1,2,3和集合2,3的交集是1，代码和交并集类似。

/*C#写法*/
List<User> result_list = big_list.Except(small_list).ToList()

5、去重 Distinct

如同数学中中的去重，集合1,2,3和集合2,3,4的去重集是1，即从集合1,2,3中剔除集合2,3,4中出现的集合1,2,3中的元素，1,2,3中剔除2,3，故结果集是1。代码和交并集类似。

/*C#写法*/
List<User> result_list = big_list.Distinct(small_list).ToList()

6、合并 Zip

Zip函数可以按照元素顺序合并两个集合的元素组成一个装纳新元素的集合，集合1,2,3和集合2,3,4，可以合并成12,23,34这种字符串拼接的集合，也可以合并成{1,2},{2,3},{3,4}这种新的匿名对象集合。

/*C#写法*/
//合并为字符串拼接的新字符串集合
IEnumerable<string> unionList = list.Zip(salaryList, (i1, i2) => i1.name + ", " + i2.occupation); 
//合并为新的匿名对象集合
var unionList = list.Zip(salaryList, (i1, i2) => new { n = i1.name, m = i2.occupation });

三、LINQ表达式多表查询总结

LINQ (Language Integrated Query) 在处理多表查询时展现了其强大和灵活的能力，类似于SQL中的JOIN操作。这种多表连接查询在数据库操作中尤为重要，因为它允许开发者从多个数据源中组合和检索数据。利用LINQ进行多表查询不仅可以提高数据处理的效率，还可以在查询时直接利用C#或VB.NET的语法特性，使代码更加直观和易于维护。

多表查询的使用场景：