C#进阶-LINQ表达式之多表查询(Join连接篇)

一、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的Join连接语法示例

1、Join连接查询分类

SQL中常见的连接查询有：

• left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
• right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
• inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
• full join : 外连接，返回两个表中的行：left join + right join。
• cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

Linq只有Join这个函数。

Linq中的Join连接查询是通过调换关联表和被关联表的顺序来转换左右连接的方向，通过调整Where和On等条件筛选函数的位置，来改变逻辑，实现更复杂的内连接全连接等功能。

同样，Linq的join方法也有两种写法：

/* C#写法1 <Linq写法>*/
IEnumerable<Salary> SalaryList = 
  from u in list
  join s in salaryList 
  on u.id equals s.user_id
  select s;

/*C#写法2 <Lambda写法>*/
IEnumerable<Salary> SalaryList = list
  .Join(
    inner: salaryList, /*inner: 可以省略*/
    outerKeySelector: u => u.id, /*outerKeySelector: 可以省略*/
    innerKeySelector: s => s.user_id, /*innerKeySelector: 可以省略*/
    resultSelector: (u, s) => s /*resultSelector: 可以省略*/
  );

2、单条件Join连接查询

如上面两张表，数据表2的user_id是数据表1的外键，对应数据表1的id，可以通过关联查询把两张表不同的属性通过用户一一对应。

我们举个例子，利用关联查询查询表1用户信息和对应在表2的薪资信息：

/* SQL里的表达: 查询所有用户的姓名、年龄、职业、性别、是否在职和薪资*/
SELECT u.id, u.name, u.age, u.occupation, u.gender, s.active, s.salary 
FROM 
User AS u 
LEFT JOIN 
Salary  AS s 
ON u.id = s.user_id;

/* C#写法1*/
IEnumerable<UserSalary> UserSalaryList =
  from u in list
  join s in salaryList on u.id equals s.user_id
  select new UserSalary
  {
    id = u.id,
    name = u.name,
    age = u.age,
    occupation = u.occupation,
    gender = u.gender,
    active = s.active,
    salary = s.salary
  };
/*C#写法2*/
IEnumerable<UserSalary> UserSalaryList = list
  .Join(salaryList, u => u.id, s => s.user_id, (u, s) => new UserSalary
  {
    id = u.id,
    name = u.name,
    age = u.age,
    occupation = u.occupation,
    gender = u.gender,
    active = s.active,
    salary = s.salary
  });

/* 遍历 输出 */
foreach (UserSalary user in UserSalaryList)
{
  Console.WriteLine(PrintUserSalaryObject(user));
}

 /* 输出结果 */
{id = 1, name = Zhang Long, age = 38, gender = True, occupation = Teacher, active = True, salary = 7800}
{id = 2, name = Zhang Jin, age = 18, gender = False, occupation = Student, active = True, salary = 1500}
{id = 3, name = Zhang Shuai, age = 38, gender = False, occupation = Teacher, active = False, salary = 8800}
{id = 4, name = Liu Guangzhi, age = 38, gender = False, occupation = Doctor, active = True, salary = 12800}
{id = 5, name = Liu Ziming, age = 38, gender = True, occupation = Doctor, active = True, salary = 13600}
{id = 6, name = Liu Shuai, age = 29, gender = False, occupation = Doctor, active = False, salary = 29000}
{id = 7, name = Liu Jin, age = 21, gender = True, occupation = Builder, active = True, salary = 7000}
{id = 8, name = Jiang Long, age = 38, gender = True, occupation = Builder, active = False, salary = 8500}
{id = 9, name = Hu Ziming, age = 21, gender = True, occupation = Student, active = True, salary = 2100}
{id = 10, name = Hu Jin, age = 21, gender = False, occupation = Student, active = True, salary = 1300}

3、多条件Join连接查询

/* SQL里的表达: 用name和id两个属性关联用户表和薪资表，
查询所有用户中性别是男性且在职的工资信息*/
/* SQL写法1*/
SELECT * FROM User AS u 
RIGHT JOIN Salary AS s 
ON u.id = s.user_id AND u.name = s.name 
AND u.gender = true AND s.active = true;

/* SQL写法2*/
/*把筛选条件放到JoinOn后面的Where里可以避免左右连接
带来的半壁空值的困扰，相当于先关联再筛选*/
SELECT * FROM User AS u 
RIGHT JOIN Salary AS s 
ON u.id = s.user_id AND u.name = s.name 
WHERE u.gender = true AND s.active = true;

/* C#写法1*/
/*这种写法不推荐，结果集会有null，
推荐关联后取得数据再做筛选，
即把筛选条件写在Join后的结果集处理里*/
IEnumerable<Salary> JointList = ( 
  from r1 in list
  where r1.gender
  join r2 in (from r3 in salaryList 
  where r3.active select r3) 
  on new 
  {
    ID = r1.id,
    r1.name
  }
  equals new
  {
    ID = r2.user_id,
    r2.name
  }
  into cls
  from c in cls.DefaultIfEmpty()
  select c
).ToList();

/*C#写法2*/
IEnumerable<Salary> JointList = ( 
  from r1 in list
  where r1.gender
  join r2 in salaryList 
  on new 
  {
    ID = r1.id,
    r1.name
  }
  equals new
  {
    ID = r2.user_id,
    r2.name
  }
  into cls
  from c in cls.DefaultIfEmpty()
  where c.active 
  select c
).ToList();

/*C#写法3*/
IEnumerable<Salary> JointList = (
  from r1 in list
  from r2 in salaryList
  where
  r2.active && 
  r1.id == r2.user_id && 
  r1.name == r2.name && 
  r1.gender 
  select r2
).ToList();

/*C#写法4 <Lambda写法>*/
IEnumerable<Salary> JointList = 
  list.Where(u => u.gender)
  .Join(
    salaryList.Where(s => s.active),
    u => new { ID = u.id, u.name },
    s => new { ID = s.user_id, s.name },
    (u, s) => s
  );

/*C#写法5 <Lambda写法>*/
/*把方法4对Salary集合的筛选放在整个连接查询的后面，
因为gender是User的专有属性，所以gender的筛选不能
放到内容对象为Salary的结果集后面*/
IEnumerable<Salary> JointList = 
  list.Where(u => u.gender)
  .Join(
    salaryList,
    u => new { ID = u.id, u.name },
    s => new { ID = s.user_id, s.name },
    (u, s) => s
  ).Where(s => s.active);

/* 遍历 输出 */
foreach (Salary salary in JointList)
{
  if(salary != null)
  Console.WriteLine(PrintUserSalaryObject(salary));
}

 /* 输出结果 */
{id = 1, name = Zhang Long, occupation = Teacher, active = True, salary = 7800}
{id = 5, name = Liu Ziming, occupation = Doctor, active = True, salary = 13600}
{id = 7, name = Liu Jin, occupation = Builder, active = True, salary = 7000}
{id = 9, name = Hu Ziming, occupation = Student, active = True, salary = 2100}

4、自定义条件Join连接查询

/*Linq自定义条件的Join语法*/
public static System.Collections.Generic.IEnumerable<TResult> Join<TOuter,TInner,TKey,TResult> (
    this System.Collections.Generic.IEnumerable<TOuter> outer, 
    System.Collections.Generic.IEnumerable<TInner> inner, 
    Func<TOuter,TKey> outerKeySelector, //结果Tkey需要包含下列比较器需要的参数
    Func<TInner,TKey> innerKeySelector, //结果Tkey需要包含下列比较器需要的参数
    Func<TOuter,TInner,TResult> resultSelector, 
    System.Collections.Generic.IEqualityComparer<TKey> comparer);
    /*尤其注意这里相等比较器的传入参数是TKey类型，
    需和outerKeySelector和innerKeySelector的TKey类型保持一致*/

举个例子：

根据相同的姓名和职业关联User和Salary信息，查询满足雄性且在职的人的Salary信息。

/*新建一个相等比较器CompareUser*/
/*当两个User对象的name和occupation属性相等时返回true，否则为false*/
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;
    }
}

/*Linq表达式实现找到符合CompareUser相等比较器的判等要求并且满足若干条件*/
IEnumerable<Salary> JointList = list.Where(u => u.gender)
  .Join<User, Salary, User, Salary>(  
    inner: salaryList,
    /*因为比较器用到了name和occupation两个属性，所以这里的Selector要包含这两个有用的属性值*/
    outerKeySelector: u => new User{ name = u.name, occupation = u.occupation},
    innerKeySelector: s => new User{ name = s.name, occupation = s.occupation },
    resultSelector: (u, s) => s,
    comparer: new CompareUser()
).Where(s => s.active);

/* 遍历 输出 */
foreach (Salary salary in JointList)
{
  if(salary != null)
  Console.WriteLine(PrintUserSalaryObject(salary));
}

 /* 输出结果 */
{id = 1, name = Zhang Long, occupation = Teacher, active = True, salary = 7800}
{id = 5, name = Liu Ziming, occupation = Doctor, active = True, salary = 13600}
{id = 7, name = Liu Jin, occupation = Builder, active = True, salary = 7000}
{id = 9, name = Hu Ziming, occupation = Student, active = True, salary = 2100}

5、GroupJoin连接查询带分组

GroupJoin和Join的区别在于结果集进行了GroupBy分组，这里直接举一个多条件、自定义的最难例子，因为就是Join语法和GroupBy语法的结合体，不难理解，不多叙述。

/* GroupJoin语法 */
IEnumerable<ListMultiGroupResult2> JointList = list.Where(u => u.gender)
  .GroupJoin(
  inner: salaryList.Where(s => s.active),
  outerKeySelector: u => new User { name = u.name, occupation = u.occupation },
  innerKeySelector: s => new User { name = s.name, occupation = s.occupation },
  resultSelector: (u, s) => new ListMultiGroupResult2 { 
    Occupation = u.occupation, 
    Name = u.name, 
    SalaryList = s.ToList() },
  comparer: new CompareUser()
  );

/*自定义相等比较器CompareUser*/
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;
    }
}

/* 遍历 输出 */
foreach (ListMultiGroupResult2 s in JointList)
{
  Console.WriteLine(s.Occupation + "/" + s.Name);
  foreach (Salary salary in s.SalaryList)
  {
    Console.WriteLine(PrintSalaryObject(salary));
  }
}

 /* 输出结果 */
Teacher/Zhang Long
{id = 1, name = Zhang Long, occupation = Teacher, active = True, salary = 7800}
Doctor/Liu Ziming
{id = 5, name = Liu Ziming, occupation = Doctor, active = True, salary = 13600}
Builder/Liu Jin
{id = 7, name = Liu Jin, occupation = Builder, active = True, salary = 7000}
Builder/Jiang Long
Student/Hu Ziming
{id = 9, name = Hu Ziming, occupation = Student, active = True, salary = 2100}

三、LINQ表达式Join连接查询总结

LINQ (Language Integrated Query) 提供了强大的Join连接查询功能，这在多表查询中尤为重要。在数据库或其他数据源操作中，Join操作使得从多个表中组合数据变得可能，极大地增强了数据处理的灵活性和效率。通过使用C#或VB.NET的语法，LINQ Join查询不仅简化了复杂的查询逻辑，还提高了代码的可读性和维护性。

多表查询的使用场景：

• 数据整合：连接查询允许合并来自不同数据表的信息，提供全面的数据视图，非常适用于报表和综合分析。
• 数据关联：通过连接用户信息与订单信息等，可以更深入地分析用户行为和购买模式。
• 复杂数据处理：Join操作是复杂查询设计中的关键部分，特别是在处理需要多源数据聚合的场景中。

LINQ的Join查询提供了一个非常强大且灵活的工具集，以处理多源数据的复杂关联和整合。正确使用这些工具不仅可以优化数据处理流程，还能显著提升数据查询的效率和质量。随着数据量的增加和查询需求的复杂化，LINQ Join查询在日常的数据操作和分析中展现出其不可替代的价值。