Entity Framework Core 如何查询用户含组层级继承的所有角色

前置优化提醒 #

你的User实体中GroupId字段需要调整为可空类型，否则顶级用户组没有上级节点时无法正常存储：

public Guid? GroupId { get; set; }

方案1：迭代遍历（适合层级较浅的场景） #

写法简单易理解，不需要写原生SQL，适合组层级最多不超过5层的场景：

// 输入参数：待查询用户Id userId
// 1. 获取目标用户基础信息
var targetUser = await context.Users.FirstAsync(u => u.Id == userId);
// 2. 收集所有需要查询角色的主体：用户本身 + 所有上级组
var subjectList = new List<(Guid SubjectId, string SourceName)>
{
    (targetUser.Id, "直接分配") // 用户直接绑定的角色来源标记为直接分配
};

// 迭代向上遍历所有上级组
var currentGroupId = targetUser.GroupId;
while (currentGroupId.HasValue)
{
    var group = await context.Users.FirstOrDefaultAsync(u => u.Id == currentGroupId.Value && u.Type == "Group");
    if (group == null) break;
    subjectList.Add((group.Id, group.Name));
    currentGroupId = group.GroupId;
}

// 3. 批量查询所有主体绑定的角色，返回要求的字段
var roleResult = await context.UserRoles
    .Where(ur => subjectList.Select(s => s.SubjectId).Contains(ur.UserId))
    .Select(ur => new 
    {
        RoleName = ur.Role.Name,
        InheritFrom = subjectList.First(s => s.SubjectId == ur.UserId).SourceName
    })
    .ToListAsync();

方案2：递归CTE查询（适合层级较深的高性能场景） #

EF Core 8及以上版本支持原生SQL结合LINQ查询，直接在数据库端完成递归层级遍历，只需要一次数据库请求，性能更高：

// 输入参数：待查询用户Id userId
var roleResult = await context.UserRoles
    .FromSql($"""
        WITH RecursiveSubjects AS
        (
            -- 初始节点：目标用户
            SELECT Id, GroupId
            FROM Users
            WHERE Id = {userId}
            UNION ALL
            -- 递归节点：向上匹配所有上级组
            SELECT u.Id, u.GroupId
            FROM Users u
            INNER JOIN RecursiveSubjects rs ON u.Id = rs.GroupId
            WHERE u.Type = 'Group'
        )
        SELECT ur.*
        FROM UserRoles ur
        INNER JOIN RecursiveSubjects rs ON ur.UserId = rs.Id
    """)
    .Select(ur => new 
    {
        RoleName = ur.Role.Name,
        InheritFrom = ur.UserId == userId ? "直接分配" : ur.User.Name
    })
    .ToListAsync();

注：EF Core会自动对插值的{userId}做参数化处理，不存在SQL注入风险。

结果处理说明 #

• 如果同一个角色同时来自多个来源（比如用户直接分配了该角色，同时所属组也分配了同一个角色），上述查询会返回多条记录，若需要去重可在查询最后添加 .DistinctBy(x => x.RoleName) 仅保留唯一角色
• 如果你不需要保留所有来源，仅需要角色列表，可调整返回的匿名类结构，只保留RoleName字段即可

内容的提问来源于stack exchange，提问作者Haha