How join tables with many to many relationships

Question

What I'm trying to do is to is join tables to fill a viewModel that looks like this:

public class UserViewModel
{
    public String Id { get; set; }
    public String UserName { get; set; }
    public String Email { get; set; }
    public String Role { get; set; }
}

My query atm looks like this, but it doesn't work obviously, but it might help with descriping the problem.

public IActionResult AddAdmin()
{
    var allUsers = (from u in _dbContext.Users
                    join r in _dbContext.UserRoles on u.Id equals r.UserId
                    join i in _dbContext.Roles on r.RoleId equals i.Id
                    select new UserViewModel
                 {
                     UserName = u.UserName,
                     Email = u.Email,
                     Id = u.Id,
                     Role = i.Name
                 }).ToList();

    return View(allUsers);
}

As you see the thing I find hard is to apply the role to the viewModel, since they are connected to eachother with a manyTomany Table

Problem: The query does not work, and does not give anything in result Before I joined the role into the viewModel, i got the data to the view, now i get nothing at all.

My Question: What's the correct way to do this? To easily navigate through the data

Answer 1

When I did it like this, it worked...

public IActionResult AddAdmin()
{
    var allUsers = (from u in _dbContext.Users
                    select new UserViewModel
                 {
                     UserName = u.UserName,
                     Email = u.Email,
                     Id = u.Id

                 }).ToList();

    foreach (var item in allUsers)
    {
        var roleId = _dbContext.UserRoles.Where(x => x.UserId == item.Id).FirstOrDefault();
        item.Role = _dbContext.Roles.Where(x => x.Id == roleId.RoleId).FirstOrDefault().Name;
    }
    return View(allUsers);
}

I know it looks like s***..

Answer 2

You need a third entity to tie the two together. In Database terms, it is called a junction table.