How to get the number of users in a role

Question

I want to get the number of users that are assigned to a role, something like this see pic

I also checked this post but didn't helped me.

My RoleViewModel

public class RolesViewModel
{
    public RolesViewModel() { }

    public RolesViewModel(ApplicationRole role)
    {
        Id = role.Id;
        RoleName = role.Name;


    }

    public string Id { get; set; }

    [Required]
    [Display(Name = "Role Name")]
    public string RoleName { get; set; }

    public int Count { get; set; }


}

Index View

 @model IEnumerable<CMSRPPF.Areas.Admin.Models.RolesViewModel>
@{
    ViewBag.Title = "Index";
    Layout = "~/Areas/Admin/Views/Shared/_LayoutDashboard.cshtml";
}

<h2>Roles</h2>

<p>


 @Html.ActionLink("Add Role", "Create") 
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.RoleName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Count)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.RoleName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Count)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id = item.Id }) |
            @Html.ActionLink("Details", "Details", new { id = item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id = item.Id })
        </td>
    </tr>
}

</table>

Controller

   public ActionResult Index()
{
    ApplicationDbContext db = new ApplicationDbContext();

    List<RolesViewModel> list = new List<RolesViewModel>();


    foreach (var role in RoleManager.Roles)
        {

        var r = new RolesViewModel(role)
            {

                RoleName = role.Name,              
                Count = db.Users.Where(x=>x.Roles.Select(roles => roles.RoleId).Contains("4738a87a-4461-4b54-828b-1c5543f13bb2")).ToList().Count()

        };

            list.Add(r);

        }

    return View(list);

}

By now I managed to count the users based on RoleId for a specific role, but I don't know how to do it for all of them. If someone can help me please, I'll really appreciate it.

Answer 1

Probably there is a better way to do it, but at least this seems to work on my db.

var result = from a in db.Roles
             group a.Name by new { a.Id, a.Name } into g
             select new RolesViewModel
             {
                 Id = g.Key.Id,
                 RoleName = g.Key.Name,
                 Count = db.Users.Count(x => x.Roles.Select(k => k.RoleId).Contains(g.Key.Id))
             }.ToList();

Now result is a List of RolesViewModel with the property count representing the number of user with a specific rols. Consider that you can have a user with more than one user so the sum of counts could be different from the sum of users.