construct new object of 3 relations-CodePudding

I have database schema of 4 tables. Tenants can have many users, users can have many tenants. User can have many roles in tenant.

Using EF Core, I need to get all users by tenant_id. User DTO need to contain list of roles. So far, I thought of something like this:

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

Models:

[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; }
}

But I want this DTO to have list of roles and user Ids not repeating in user list. Is this possible in 1 query or should I iterate over this user list and construct new list?

CodePudding user response：

Try the following query:

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,
        Role = u.UserTenantsRoles
            .Where(utr => utr.TenantId == tenantId)
            .Select(utr => utr.Role.Name)
            .ToList()
    });