EF Core按TenantId查询用户，生成含角色列表的无重复DTO

现有包含4张表的数据库架构：Tenants与Users为多对多关系，User在Tenant中可拥有多个Role。需要使用EF Core按tenant_id获取所有用户，要求返回的GetUserDto包含角色列表，且用户ID不重复。

当前实现会导致用户重复且仅返回单个角色，询问能否通过单条EF Core查询实现需求，还是需要遍历结果重新构造列表。

当前实现代码 #

查询代码 #

var users = dbContext.UserTenantsRoles
                     .Where(u => u.TenantId == tenantId)
                     .Select(u => new GetUserDto
                                  {
                                      Id = u.User.Id,
                                      Email = u.User.Email,
                                      CreatedAt = u.User.CreatedAt,
                                      Role = u.Role.RoleName.ToString()
                                  });

DTO定义 #

public class GetUserDto
{
    public Guid Id { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
    public string Role { get; set; }
}

实体模型 #

[Index(nameof(Email), IsUnique = true)]
public class User
{
    public Guid Id { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime? ModifiedAt { get; set; }

    public List<UserTenantRole> UserTenantsRoles { get; set; }
}

[Index(nameof(Name), IsUnique = true)]
public class Tenant
{
    public Guid Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime? ModifiedAt { get; set; }
    public DateTime? DeletedAt { get; set; }
    public string Name { get; set; }

    public List<UserTenantRole> UserTenantsRoles { get; set; }
}

[Index(nameof(RoleName), IsUnique = true)]
public class Role
{
     public Guid Id { get; set; }
     public RoleName RoleName { get; set; }

     public List<UserTenantRole> UserTenantsRoles { get; set; }
}

public enum RoleName
{
    User,
    Admin
}

public class UserTenantRole
{
    public Guid TenantId { get; set; }
    public Tenant Tenant { get; set; }

    public Guid UserId { get; set; }
    public User User { get; set; }

    public Guid RoleId { get; set; }
    public Role Role { get; set; }
}

可以通过单条EF Core查询直接实现需求，无需遍历结果重新构造列表，具体步骤如下：

1. 修改DTO结构 #

首先需要将GetUserDto中的单个Role字段替换为角色列表，适配多角色场景：

public class GetUserDto
{
    public Guid Id { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
    // 替换单个Role为角色列表，初始化避免空引用
    public List<string> Roles { get; set; } = new List<string>();
}

2. 两种EF Core查询实现方式 #

方式一：从UserTenantRole分组查询 #

通过按用户唯一标识分组，聚合该用户在当前租户下的所有角色：

var users = dbContext.UserTenantsRoles
    .Where(utr => utr.TenantId == tenantId)
    .GroupBy(utr => new 
    { 
        utr.User.Id, 
        utr.User.Email, 
        utr.User.CreatedAt 
    })
    .Select(g => new GetUserDto
    {
        Id = g.Key.Id,
        Email = g.Key.Email,
        CreatedAt = g.Key.CreatedAt,
        Roles = g.Select(utr => utr.Role.RoleName.ToString()).ToList()
    })
    .ToList();

方式二：从User实体开始查询 #

通过导航属性关联当前租户的UserTenantRole，直接筛选并获取用户的角色列表：

var users = dbContext.Users
    .Where(u => u.UserTenantsRoles.Any(utr => utr.TenantId == tenantId))
    .Select(u => new GetUserDto
    {
        Id = u.Id,
        Email = u.Email,
        CreatedAt = u.CreatedAt,
        Roles = u.UserTenantsRoles
            .Where(utr => utr.TenantId == tenantId)
            .Select(utr => utr.Role.RoleName.ToString())
            .ToList()
    })
    .ToList();

以上两种方式都会生成单条SQL查询（可通过EF Core日志验证），直接返回去重后的用户列表，每个用户包含其在当前租户下的所有角色。

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