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

Damon小智

发布于 2024-02-03 10:29:25

2130

发布于 2024-02-03 10:29:25

本篇文章我们将演示LINQ扩展包基础语法里的多表查询，以Join连接查询为主要内容。目前LINQ支持两种语法，我会在每个案例前先用大家熟知的SQL语句表达，再在后面用C#的两种LINQ语法分别实现。LINQ语法第一次接触难免感到陌生，最好的学习方式就是在项目中多去使用，相信会有很多感悟。

多表查询Ⅱ

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

数据源1：

数据源2：

① 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: 可以省略*/
  );

② 单条件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}

③ 多条件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}

④ 自定义条件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}

⑤ 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}

Damon_Liu

Damon, Chinese, Liu Guangzhi, Software development engineer, CSDN quality creator, Ali Cloud expert blogger, Microsoft Technology Associate, Good at C#, Java, PHP, Python, etc, Love sports, Workaholic, Communist.

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-1-11，如有侵权请联系 cloudcommunity@tencent.com 删除

连接