Getting a list of users with their assigned role i

Question

I am stuck trying to get this output

Id | Name   | Role
----------------------------
1  | John   | Administrator
----------------------------
2  | Mary   | Manager
----------------------------
3  | Sage   | Editor
----------------------------
4  | Hank   | Manager

I can make it work in LINQPad, but somehow I am unable to translate it to ASP.NET MVC.

from u in Users
from ur in u.Roles
join r in Roles on ur.RoleId equals r.Id
select new {
    Id = u.Id,
    Name = u.Name,
    Role = r.Name,
}

How do I LINQ that in ASP.NET MVC 5 with Identity?

Just to be clear, I am looking for the JOIN query between Users and Roles.

Answer 1

If you are using ASP.NET Identity 2, you have to add some codes to AccountContoller. Add an ActionResult to get UserList. You also nedd ApplicationDbContext instance and get it from OwinContext :

public class AccountController : Controller
{
    private ApplicationUserManager _userManager;
    private ApplicationSignInManager _signInManager;

    public AccountController(ApplicationUserManager userManager, ApplicationSignInManager signInManager)
    {
        UserManager = userManager;
        SignInManager = signInManager;
    }

    public ActionResult UserList()
    {
        var applicationDbContext = HttpContext.GetOwinContext().Get<ApplicationDbContext>();
        var users = from u in applicationDbContext.Users
                    from ur in u.Roles
                    join r in ApplicationDbContext.Roles on ur.RoleId equals r.Id
                    select new
                    {
                        u.Id,
                        Name = u.UserName,
                        Role = r.Name,
                    };

            // users is anonymous type, map it to a Model 
            return View(users);
    }
    .
    .
    .
}

Update - if user has multiple roles:

from user in applicationDbContext.Users
select new
{
    user.Id,
    user.UserName,
    Roles = applicationDbContext.Roles.Where(r => user.Roles.Select(ur => ur.RoleId).Contains(r.Id)).Select(r => r.Name)
}