ASP.Net MVC List of User and their Roles

Question

I'm using ASP.NET MVC Default project. In there we have a database called AspNetRoles (defines roles like Admin, User)

then there's a database called AspNetUsers which just defines Users when you register them

and the last and most important is AspNetUserRoles. When you assign Roles to the User here is where it is placed (and the Id's are hashed)

My question is, how do I get a list of every user on the system and their roles? I can't figure out how to start doing this. Should I create my own User and Role database and forget about the default one? Or is there a way of creating with the default ones?

---

Also, I would keep in mind that as of now I'm assigning Roles when registering (User by default) and later when I create the Index where I show the list of Users and Roles, I will want to edit them, but this is just a disclaimer. Thank you very much for any type of help.

Answer 1

There are no built in methods for retrieving the list of users with their roles so you can try to get the user and their role like below (using EF):

var list = context.Users        
    .Where(x => x.Roles.Select(a => a.Id))
    .ToList();

Using this you get every users and assigned roles.

Update Create a Model like below first:

public class UserRoleInfo
{
    public ApplicationUser User { set; get; }
    public List<string> Roles { set; get; } 
}

Then Your Query would be like (Query Syntax):

var listOfUsers = (from u in db.Users
             let query = (from ur in db.Set<IdentityUserRole>()
                          where ur.UserId.Equals(u.Id)
                          join r in db.Roles on ur.RoleId equals r.Id select r.Name)
                          select new UserRoleInfo() {User = u, Roles = query.ToList<string>()})
                         .ToList();

Query above will return Every User With their Roles no matter how many.

UPDATE

Your view would be like:

@model UserRoleInfo
    <table>
        <tbody>
@foreach (var user in Model)
{
    var appUser = user.ApplicationUser;
    var roles = user.Roles;

            <tr>
                <td>
                    @appUser.Email
                </td>
                <td>
                    @string.Join(",", roles)
                </td>
            </tr>

}
        </tbody>
    </table>

Answer 2

I would suggest going this way since this will return all users from database as a list with their respective roles attached in the user object. Furthermore it uses eagerly loading on Roles entity, so it will be converted to a join in database.

If you want to take only one user and its roles where is just a where condition to add and filter by its id.

var users = _applicationDbContext.Users.Include(x => x.Roles).ToList();
foreach (var applicationUser in users)
{
    var userRoles = applicationUser.Roles;
    //Do something with this user roles
}

Answer 3

to get a list of every user on the system and their roles:

SELECT        UserRoleId, UserId, RoleId, dbo.GetRoleName(RoleId) AS RoleName
FROM            dbo.UserRoles

Function : dbo.GetRoleName -

function [dbo].[GetRoleName](@input int)
Returns Nvarchar(500)
As
Begin
Declare @RoleName nvarchar(500)
Set @RoleName=(select Name from Roles where RoleId=@input)
return @RoleName
End