如何将含嵌套连接的三表左外连接SQL转换为C# LINQ？

我有三张表（角色表、用户表、关联表），需要展示所有角色，同时显示特定用户是否属于该角色（若属于则显示其名称）。以下是适用于SQL Server的嵌套连接SQL语句，我使用适配.Net 4.7.2的Entity Framework 6，DbContext中的表名称为DbSet<tRole> tRoles，请问如何将该SQL转换为C# LINQ？

原始SQL示例 #

create table tRole (id int, name varchar(255))
insert into  tRole values (1,'user')
insert into  tRole values (2,'admin')

create table tUser (id int, name varchar(255))
insert into tUser values (1,'john')
insert into tUser values (2,'sam')
insert into tUser values (3,'amanda')

create table tRoleUser (roleId varchar(255), userId varchar(255))
insert into tRoleUser values (1,1)  -- user john
insert into tRoleUser values (1,2)  -- user sam
insert into tRoleUser values (2,2)  -- admin sam
-- amanda not user, and not admin

select 
    r.name [role name],
    ru.userid,
    u.name [user name]
from tRole r
left join tRoleUser ru
     join tUser u on u.id = ru.userid and u.name = 'john'
   on r.id = ru.roleid

预期查询结果 #

查询john的结果 #

user    1       john
admin   NULL    NULL

查询amanda的结果 #

user    NULL    NULL
admin   NULL    NULL

查询sam的结果 #

user    2   sam
admin   2   sam

LINQ转换实现 #

假设你的实体类已配置好导航属性（tRole关联tRoleUsers，tRoleUser关联tUser），可以用以下几种方式实现：

方式1：查询语法（对应原始SQL的嵌套连接逻辑） #

// 目标用户名
string targetUserName = "john";

using (var db = new YourDbContext())
{
    var query = from r in db.tRoles
                // 子查询：筛选出目标用户的角色关联记录
                join ru in (
                    from ruJoin in db.tRoleUsers
                    join uJoin in db.tUsers on ruJoin.userId equals uJoin.id
                    where uJoin.name == targetUserName
                    select ruJoin
                ) on r.id equals ru.roleId into roleUserGroup
                // 左连接：没有匹配时返回null
                from ru in roleUserGroup.DefaultIfEmpty()
                select new 
                {
                    RoleName = r.name,
                    UserId = ru != null ? (int?)ru.userId : null,
                    UserName = ru != null ? ru.tUser.name : null
                };

    var result = query.ToList();
}

方式2：方法语法（链式调用） #

string targetUserName = "john";

using (var db = new YourDbContext())
{
    // 子查询：获取目标用户的角色关联记录
    var roleUserSubquery = db.tRoleUsers
        .Join(db.tUsers, 
              ru => ru.userId, 
              u => u.id, 
              (ru, u) => new { ru, u })
        .Where(x => x.u.name == targetUserName)
        .Select(x => x.ru);

    // 主查询：左连接所有角色和子查询结果
    var query = db.tRoles
        .GroupJoin(roleUserSubquery, 
                   r => r.id, 
                   ru => ru.roleId, 
                   (r, ruGroup) => new { r, ruGroup })
        .SelectMany(x => x.ruGroup.DefaultIfEmpty(), 
                    (x, ru) => new 
                    {
                        RoleName = x.r.name,
                        UserId = ru != null ? (int?)ru.userId : null,
                        UserName = ru != null ? ru.tUser.name : null
                    });

    var result = query.ToList();
}

方式3：利用导航属性简化查询 #

如果实体类已正确配置导航属性，这种方式更简洁直观，EF会自动生成等价的SQL语句：

string targetUserName = "john";

using (var db = new YourDbContext())
{
    var query = from r in db.tRoles
                select new
                {
                    RoleName = r.name,
                    UserId = r.tRoleUsers
                        .Where(ru => ru.tUser.name == targetUserName)
                        .Select(ru => (int?)ru.userId)
                        .FirstOrDefault(),
                    UserName = r.tRoleUsers
                        .Where(ru => ru.tUser.name == targetUserName)
                        .Select(ru => ru.tUser.name)
                        .FirstOrDefault()
                };

    var result = query.ToList();
}

以上三种方式都能实现原始SQL的逻辑，返回所有角色，并在目标用户属于该角色时显示其ID和名称，否则返回NULL。

内容的提问来源于stack exchange，提问作者sam sergiy klok