C#进阶-LINQ表达式之多表查询Ⅰ

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

多表查询Ⅰ

在学习之前，我们要做一些准备工作，我们需要创建User对象和包含User对象的集合，创建Salary对象和包含Salary对象的集合，作为后面查询和输出的数据源，参见这篇文章C#进阶之LINQ表达式总结完成准备工作。

数据源1：

数据源2：

---

① 交集 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}

---

② 并集 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}

---

③ 全集 Concat

不同于并集(Union)去除了两个集合重复的元素，Concat保留重复的元素。
比如，{1,2,3}和{3,4,5}的Union结果是{1,2,3,4,5}，而Concat的结果是{1,2,3,3,4,5}。

---

④ 差集 Except

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

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

---

⑤ 去重 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()

⑥ 合并 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 });