EF Core递归查询角色树：如何避免多次数据库查询？

问题背景 #

当前通过Entity Framework Core递归构建角色树形结构时，每次递归都会触发新的数据库查询，形成N+1性能问题。且角色的父子关联通过中间表RoleParent实现，而非实体直接包含子集合，常规单实体树形优化方案不适用。

优化思路 #

一次性将所有角色及其关联的父子关系数据加载到内存中，再在内存中构建树形结构，彻底避免多次数据库查询。

优化实现代码 #

1. 一次性加载全量数据 #

执行一次数据库查询，加载所有角色及其关联的子角色关系数据：

public async Task<List<RoleTreeDTO>> BindRolesTree()
{
    // 一次性加载所有角色，以及它们作为父角色对应的子角色关联
    var allRoles = await CoreService.Table()
        .Include(r => r.RoleParentParents)
            .ThenInclude(rp => rp.Role) // 加载每个关联对应的子角色
        .ToListAsync();

    // 转换为DTO并构建树形结构
    return BuildRoleTree(allRoles);
}

2. 内存中构建树形结构 #

通过字典映射快速查找DTO，避免递归查询数据库：

private List<RoleTreeDTO> BuildRoleTree(List<Role> allRoles)
{
    // 1. 将所有角色转换为DTO，并建立ID到DTO的映射字典
    var roleDtoDict = allRoles.ToDictionary(
        role => role.Id,
        role => new RoleTreeDTO
        {
            Id = role.Id,
            title = role.Name ?? string.Empty,
            Gcode = role.Gcode,
            children = new List<RoleTreeDTO>()
        }
    );

    // 2. 遍历每个角色，为其添加子角色DTO
    foreach (var parentRole in allRoles)
    {
        var parentDto = roleDtoDict[parentRole.Id];
        foreach (var roleParent in parentRole.RoleParentParents)
        {
            if (roleDtoDict.TryGetValue(roleParent.Role.Id, out var childDto))
            {
                parentDto.children.Add(childDto);
            }
        }
    }

    // 3. 筛选出顶级角色（没有父角色的角色）
    var childRoleIds = allRoles.SelectMany(r => r.RoleParentParents.Select(rp => rp.Role.Id))
        .ToHashSet();
    var topLevelRoles = roleDtoDict.Values
        .Where(dto => !childRoleIds.Contains(dto.Id.Value))
        .ToList();

    return topLevelRoles;
}

优化说明 #

• 减少数据库查询次数：从原来的N+1次查询变为仅1次查询，大幅降低数据库IO开销
• 内存操作高效：通过字典映射实现O(1)级别的DTO查找，构建树形结构的时间复杂度为O(n)
• 适配中间表关联结构：针对RoleParent中间表的关联逻辑，通过遍历父角色的RoleParentParents集合，准确关联子角色

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