Many-to-Many with Related ViewModel DTO C#

Question

I have 3 tables in a NetCore C # project, I cannot form a query to the database to form a complete list of users with roles attached to them

User table

var user = await _context.Users
    .Select(x => new
    {
        x.Id,
        x.UserName           
    }).ToListAsync();

Role table

var role = await _context.Roles
    .Select(x => new
    {
        RoleId = x.Id,
        RoleName = x.Name
    }).ToListAsync();

and UserRole table (relationship)

var userRole = await _context.UserRoles
    .Select(x => new
    {
        x.RoleId,
        x.UserId
    }).ToListAsync();

How do you need to build a query for this DTO to be generated?

public class UserRoleDto
{
    public int UserId { get; set; }
    public string UserName { get; set; }

    public IEnumerable<RolesDto> Roles { get; set; }

}

public class RolesDto
{
    public int RoleId { get; set; }

    public string RoleName { get; set; }
}

and this result was displayed?

[
    {
        "userId": "1",
        "userName": "User1",
        "roles": [
            {
                "roleId": "1",
                "roleName": "admin"
            },
            {
                "roleId": "2",
                "roleName": "operator"
            },
            {
                "roleId": "3",
                "roleName": "support"
            }
        ]
    },
    {
        "userId": "2",
        "userName": "User2",
        "roles": [
            {
                "roleId": "2",
                "roleName": "operator"
            }
        ]
    }
]

Answer 1

Consider not to include -

public DbSet<UserRole> UserRoles { get; set; }

in your DbContext class. That is because UserRole is a joining entity and joining entities are not a model centric concept. Their existence is purely a relational database centric idea. So, try not to query directly against the joining entity.

Define the models as -

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }

    public IList<UserRole> UserRoles { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }

    public IList<UserRole> UserRoles { get; set; }
}

public class UserRole
{
    public int UserId { get; set; }
    public int RoleId { get; set; }

    public User User { get; set; }
    public Role Role { get; set; }  
}

and your query can be like -

var result = await _context.Users.Select(
    u => new UserRoleDto
    {
        UserId = u.Id,
        UserName = u.Name,
        Roles = u.UserRoles.Select(x => new RolesDto { RoleId = x.RoleId, RoleName = x.Role.Name }).ToList()
    }).ToListAsync();

Answer 2

Change

public class UserRoleDto
{
    public int UserId { get; set; }
    public string UserName { get; set; }

    public IEnumerable<RolesDto> Roles { get; set; }

}

To

public class UserRoleDto
{
    public int UserId { get; set; }
   
 public int RoleId{ get; set; }
}

and make a query to join user and roles. After this use this post Build JSON Hierarchy from Structured Data to convert to json.

Answer 3

My first impression about this is that your join table UserRole probably looks like this:

public class UserRole
{
    public int UserId {get; set;}
    public int RoleId {get; set;}
}

Which is good, but it lack of the navigation property that does fulfill the requirement of your DTO. You should aim to have a join table as

public class UserRole
{
    public int UserId {get; set;}
    public int RoleId {get; set;}
    public Role Role {get; set;}
    public User User {get; set;}
}

You almost got the query right, It's not tested but try something like this:

var userRole = await _context.UserRoles
    .Select(x => new UserRoleDto()  
    {
       UserId = x.User.Id,
       UserName = x.User.Name,
       Roles = new List<RolesDto>()
       {
           new RolesDto()
           {
              RoleId = x.Role.Id,
              RoleName = x.Role.Name
           }
       }
    }).ToListAsync();

Answer 4

Many to Many can be configured in the OnModelCreating

modelBuilder.Entity<User>()
   .HasMany(b => b.Roles)
   .WithMany(c => c.Users)
   .Map(cs =>
   {
       cs.MapLeftKey("UserId");
       cs.MapRightKey("RoleId");
       cs.ToTable("UserToRole");
   });

Answer 5

I implemented it with the given solution

var users = await _context.Users.Select(x => new { x.Id, x.UserName }).ToListAsync();
var userRoles = await _context.UserRoles.ToListAsync();
var roles = await _context.Roles.ToListAsync();

var result = users.Select(x => new
{
    UserId = x.Id,
    x.UserName,
    Roles = userRoles
        .Where(y => y.UserId == x.Id)
        .Join(roles, r => r.RoleId, r => r.Id, (u, r) => new
        {
            RoleId = r.Id,
            RoleName = r.Name
        }).ToList()
    }); 
return Ok(result);