How to list users with role names in ASP.NET MVC 5

Question

I have default project template of ASP.NET MVC 5 web site and I am trying to list all users with role names (not IDs).

The query is:

db.Users.Include(u => u.Roles).ToList()

Then I want to print the role names with something like:

@string.Join(", ", user.Roles.Select(r => r.RoleId))

The problem is that I can reach only RoleId, not Role class where Name and other properties are stored.

I could run another select to get all roles and then use that as a lookup. Or write a join in the query directly? I am not sure how because I do not have access to the table with IdentityUserRole entities that are binding users and roles together.

The root of the problem seems to be the fact that is Roles collection of IdentityUserRole (not Role) which contains only RoleId and UserId.

public class IdentityUserRole<TKey> {
    public virtual TKey RoleId { get; set; }
    public virtual TKey UserId { get; set; }
}

I thought that if one want to do N-to-N relationship in EF they should put directly collection of Roles and then override OnModelCreating and specify the relationships. This approach seems to complicate browsing the objects from one to another.

Why they decided to include IdentityUserRole as extra entity? To be able to add extra data to the relationships? At cost of not being able to navigate from users to roles?

Answer 1

The way I do it is:

using (var userManager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationContext()))
{
    var rolesForUser = await userManager.GetRolesAsync(userId);

   // rolesForUser now has a list role classes.
}

The identity team made two managers: RoleManager for sorting out roles (not user roles though) and UserManager basically for everything authentication wise. There is also a SignInManager as well but not needed.

So UserManager finds users, creates users, deletes users, sends emails .... the list goes on.

So my Action could look like this:

    public async Task<ActionResult> GetRolesForUser(string userId)
    {
        using (
            var userManager =
                new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext())))
        {
            var rolesForUser = await userManager.GetRolesAsync(userId);

            return this.View(rolesForUser);
        }
    }

To execute raw SQL then you can do something like this:

Create the class that Entity Framework can map to, based on the output of your query:

public class UserWithRole
{
    public string UserName {get;set;} // You can alias the SQL output to give these better names
    public string Name {get;set;}
}

using (var context = new DbContext())
{
    var sql = @"
                SELECT AspNetUsers.UserName, AspNetRoles.Name 
                FROM AspNetUsers 
                LEFT JOIN AspNetUserRoles ON  AspNetUserRoles.UserId = AspNetUsers.Id 
                LEFT JOIN AspNetRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId
                WHERE AspNetUsers.Id = @Id";
    var idParam = new SqlParameter("Id", theUserId);

    var result = context.Database.ExecuteQuery<UserWithRole>(sql, idParam);
}

Pretty simple!

If you alias your SQL return columns:

SELECT AspNetUSers.UserName, AspNetRoles.Name As RoleName

Then your DTO class can look like this:

public class UserWithRole
{
    public string UserName {get;set;}
    public string RoleName {get;set;}
}

Which is obviously a lot cleaner.