CODEWITHSUNDEEP
asp.net-mvclinqentity-frameworkasp.net-identityroles
Fred Fickleberry III
Fred Fickleberry III

Reputation: 2519

Getting a list of users with their assigned role in Identity 2

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.

Upvotes: 9

Views: 7986

Answers (2)

Udara Kasun
Udara Kasun

Reputation: 2216

This will help you

    using(ApplicationDbContext db=new ApplicationDbContext())
    {
          var users = (from user in db.Users
                       from roles in user.Roles
                       join role in db.Roles
                       on roles.RoleId equals role.Id 
                       select new
                       {
                           user.UserName, 
                           role.Name
                       }).ToList(); 
    }

Upvotes: 1

Mohsen Esmailpour
Mohsen Esmailpour

Reputation: 11544

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

Upvotes: 11

Related Questions