C#进阶-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的多表连接语法示例

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的语法特性，使代码更加直观和易于维护。

多表查询的使用场景：

• 数据整合：合并来自不同数据库或数据表的信息，用于综合分析和报告。
• 数据关联：将相关数据联结在一起，如用户信息和订单信息的关联，便于进行全面的数据分析。
• 复杂的数据处理：在执行数据聚合、过滤和转换前，先通过连接操作预处理数据。

多表查询是LINQ中非常强大的功能之一，它通过提供类似SQL的查询能力，使得数据处理变得更加简单和直观。正确利用LINQ进行多表查询，不仅可以提高开发效率，还能使代码更加清晰和易于维护。无论是在数据分析还是日常的数据操作中，LINQ的多表查询都是一个不可或缺的工具，能够有效支持复杂的数据处理需求。