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

问题:

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?

回答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.

回答2:

This is how I do it with MVC 5, Identity 2.0 and a custom user and role describe by John Atten

In controller

public virtual ActionResult ListUser()
    {            
        var users = UserManager.Users;
        var roles = new List<string>();
        foreach (var user in users)
        {
            string str = "";
            foreach (var role in UserManager.GetRoles(user.Id))
            {
                str = (str == "") ? role.ToString() : str + " - " + role.ToString();
            }
            roles.Add(str);
        }
        var model = new ListUserViewModel() {
            users = users.ToList(),
            roles = roles.ToList()
        };
        return View(model);
    }

In ViewModel

public class ListUserViewModel
{
    public IList<YourAppNamespace.Models.ApplicationUser> users { get; set; }
    public IList<string> roles { get; set; }
}

And in my View

@{
    int i = 0;
}
@foreach (var item in Model.users)
{   
    @Html.DisplayFor(modelItem =>  item.Name)
    [... Use all the properties and formating you want ... and ] 
    @Model.roles[i]
    i++;
}

回答3:

I think its bad technique to execute dynamic SQL from your C# application. Below is my method:

Model:

  public class ApplicationRole : IdentityRole
  {
    public ApplicationRole() : base() { }
    public ApplicationRole(string name) : base(name) { }
    public string Description { get; set; }

  }

Controller:

using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.Owin;
using Microsoft.AspNet.Identity.EntityFramework;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;
using System.Collections.Generic;

//Example for Details.
if (id == null)
  {
    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
  }
  var role = await RoleManager.FindByIdAsync(id);
  // Get the list of Users in this Role
  var users = new List<ApplicationUser>();

  // Get the list of Users in this Role
  foreach (var user in UserManager.Users.ToList())
  {
    if (await UserManager.IsInRoleAsync(user.Id, role.Name))
    {
      users.Add(user);
    }
  }

  ViewBag.Users = users;
  ViewBag.UserCount = users.Count();
  return View(role);

View (using ApplicationRole)

    <div>
        <h4>Roles.</h4>
        <hr />
        <dl class="dl-horizontal">
            <dt>
                @Html.DisplayNameFor(model => model.Name)
            </dt>
            <dd>
                @Html.DisplayFor(model => model.Name)
            </dd>
        </dl>
        <dl class="dl-horizontal">
            <dt>
                @Html.DisplayNameFor(model => model.Description)
            </dt>
            <dd>
                @Html.DisplayFor(model => model.Description)
            </dd>
        </dl>
    </div>
    <h4>List of users in this role</h4>
    @if (ViewBag.UserCount == 0)
{
        <hr />
        <p>No users found in this role.</p>
}
    <table class="table">
        @foreach (var item in ViewBag.Users)
    {
            <tr>
                <td>
                    @item.UserName
                </td>
            </tr>
    }
    </table>

回答4:

using System.Linq;
using System.Data;
using System.Data.Entity;

            var db = new ApplicationDbContext();
            var Users = db.Users.Include(u => u.Roles);

            foreach (var item in Users)
            {
                string UserName = item.UserName;
                string Roles = string.Join(",", item.Roles.Select(r=>r.RoleId).ToList());
            }

回答5:

Thanks to @Callum Linington for his answer. Just Try to make it little bit clear for beginners like me. Here is steps to get a list of users with their roles.

1- Create a view model called "UsersWithRoles" with some properties as shown below :

2- Create a controller called "RolesController", and then add following piece of code in it.

        public ActionResult Index()
    {
        using (var context = new ApplicationDbContext())
        {
            var sql = @"
            SELECT AspNetUsers.UserName, AspNetRoles.Name As Role
            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.SqlQuery<UserWithRoles>(sql).ToList();
            return View(result);
        }

    }

and here is what the RolesController should look like :

3- Add Index page to Roles folder, and add the following code in it.

@model IEnumerable<MVC_Auth.ViewModels.UserWithRoles>
<div class="row">
    <h4>Users</h4>
    <table class="table table-hover table-responsive table-striped table-bordered">
        <th>User Name</th>
        <th>Role</th>
        @foreach (var user in Model)
        {
            <tr>
                <td>@user.UserName</td>
                <td>@user.Role</td>
            </tr>
        }
    </table>
</div>

Here is the result

Thanks.