.NET Core 2.1 Identity get all users with their associated roles

I'm trying to pull out all my Identity users and their associated roles for a user management admin page. I thought this would be reasonably easy but apparently not. I've tried following the following solution: https://stackoverflow.com/a/43562544/5392786 but it hasn't worked out so far.

Here is what I have so far:

ApplicationUser:

public class ApplicationUser : IdentityUser
{
    public List<IdentityUserRole<string>> Roles { get; set; }
}

DBContext

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

Startup Identity code

services.AddIdentity<ApplicationUser, IdentityRole>(options => options.Stores.MaxLengthForKeys = 128)
            .AddEntityFrameworkStores<ApplicationDbContext>()
            .AddDefaultTokenProviders();

Razor Page where I want to display the list:

public class IndexModel : PageModel
{
    private readonly UserManager<ApplicationUser> userManager;

    public IndexModel(UserManager<ApplicationUser> userManager)
    {
        this.userManager = userManager;
    }

    public IEnumerable<ApplicationUser> Users { get; set; }

    public void OnGetAsync()
    {
        this.Users = userManager.Users.Include(u => u.Roles).ToList();
    }
}

I get the following error when calling userManager.Users.Include(u => u.Roles).ToList();:

> MySql.Data.MySqlClient.MySqlException: 'Unknown column 'u.Roles.ApplicationUserId' in 'field list''

I have now implemented the following solution.

As CodeNotFound pointed out in the comments, IdentityUser used to have a Roles property. This is no longer the case in .NET Core. This comment/issue on GitHub seems to be the current solution for .Net Core. I have attempted to implemented it with the following code:

ApplicationUser

public class ApplicationUser : IdentityUser
{
    public ICollection<ApplicationUserRole> UserRoles { get; set; }
}

ApplicationUserRole

public class ApplicationUserRole : IdentityUserRole<string>
{
    public virtual ApplicationUser User { get; set; }
    public virtual ApplicationRole Role { get; set; }
}

ApplicationRole

public class ApplicationRole : IdentityRole
{
    public ICollection<ApplicationUserRole> UserRoles { get; set; }
}

DBContext

public class ApplicationDbContext
    : IdentityDbContext<ApplicationUser, ApplicationRole, string, IdentityUserClaim<string>,
    ApplicationUserRole, IdentityUserLogin<string>,
    IdentityRoleClaim<string>, IdentityUserToken<string>>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<ApplicationUserRole>(userRole =>
        {
            userRole.HasKey(ur => new { ur.UserId, ur.RoleId });

            userRole.HasOne(ur => ur.Role)
                .WithMany(r => r.UserRoles)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();

            userRole.HasOne(ur => ur.User)
                .WithMany(r => r.UserRoles)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();
        });
    }
}

Startup

services.AddIdentity<ApplicationUser, ApplicationRole>(options => options.Stores.MaxLengthForKeys = 128)
            .AddEntityFrameworkStores<ApplicationDbContext>()
            .AddDefaultTokenProviders();

Finally, make sure when you're using it that you eagerly load the User's UserRoles, and then the UserRole's Role like so:

this.Users = userManager.Users.Include(u => u.UserRoles).ThenInclude(ur => ur.Role).ToList();

I had an issue where the Role property of each UserRole was null and this was resolved by adding in the .ThenInclude(ur => ur.Role) part.

Microsoft doc on multi-level eager loading: https://docs.microsoft.com/en-us/ef/core/querying/related-data#including-multiple-levels

ASP Core 2.2 update

Inherent from IdentityUserRole<Guid> not string You may also need to remove the code in the ModelBuilder to get migrations working.

For dotnet core 3.1, I've been using the following general approach.

// _appContext is an instance of IdentityDbContext<ApplicationUser>

_appContext.Users
.SelectMany(
    // -- below emulates a left outer join, as it returns DefaultIfEmpty in the collectionSelector
    user => _appContext.UserRoles.Where(userRoleMapEntry => user.Id == userRoleMapEntry.UserId).DefaultIfEmpty(),
    (user, roleMapEntry) => new { User = user, RoleMapEntry = roleMapEntry })
.SelectMany(
    // perform the same operation to convert role IDs from the role map entry to roles
    x => _appContext.Roles.Where(role => role.Id == x.RoleMapEntry.RoleId).DefaultIfEmpty(),
    (x, role) => new {User = x.User, Role = role})
.ToList() // runs the queries and sends us back into EF Core LINQ world
.Aggregate(
    new Dictionary<ApplicationUser, List<IdentityRole>>(), // seed
    (dict, data) => {
        // safely ensure the user entry is configured
        dict.TryAdd(data.User, new List<IdentityRole>());
        if (null != data.Role)
        {
            dict[data.User].Add(data.Role);
        }
        return dict;
    },
    x => x);

The SQL this generates is straightforward and reasonable:

SELECT "a"."Id", 
"a"."AccessFailedCount", 
"a"."ConcurrencyStamp", 
"a"."Email", 
"a"."EmailConfirmed", 
"a"."LockoutEnabled", 
"a"."LockoutEnd", 
"a"."NormalizedEmail", 
"a"."NormalizedUserName", 
"a"."PasswordHash", 
"a"."PhoneNumber", 
"a"."PhoneNumberConfirmed", 
"a"."SecurityStamp", 
"a"."TwoFactorEnabled", 
"a"."UserName", 
"a1"."Id", 
"a1"."ConcurrencyStamp", 
"a1"."Name", 
"a1"."NormalizedName"
FROM "AspNetUsers" AS "a"
LEFT JOIN "AspNetUserRoles" AS "a0" ON "a"."Id" = "a0"."UserId"
LEFT JOIN "AspNetRoles" AS "a1" ON "a0"."RoleId" = "a1"."Id"

loops through user list and get user roles by calling _userManager.GetRolesAsync(user) function and loops through roles of user and split roles with "," in one string variable

[HttpPost]
    public async Task<IActionResult> OnPostGetPagination()
    {
       
         
        var users = await _userManager.Users.ToListAsync();
        InputModel inputModel = new InputModel();
        foreach (var v in users)
        {
            inputModel = new InputModel();
            var roles = await _userManager.GetRolesAsync(v);
            inputModel.Email = v.UserName;
            inputModel.role = "";
            foreach (var r in roles)
            {
                if (!inputModel.role.Contains(","))
                {
                    inputModel.role = r;
                }
                else
                {
                    inputModel.role = "," + r;
                }
            }
            Input2.Add(inputModel);
        }
      
        
    }

good luck

Reference comment

First is the code to get data

 public async Task<IEnumerable<AccountViewModel>> GetUserList()
        {
            var userList = await (from user in _context.Users
                                  select new
                                  {
                                      UserId = user.Id,
                                      Username = user.UserName,
                                      user.Email,
                                      user.EmailConfirmed,
                                      RoleNames = (from userRole in user.Roles //[AspNetUserRoles]
                                                   join role in _context.Roles //[AspNetRoles]//
                                                   on userRole.RoleId
                                                   equals role.Id
                                                   select role.Name).ToList()
                                  }).ToListAsync();

            var userListVm = userList.Select(p => new AccountViewModel
            {
                UserId = p.UserId,
                UserName = p.Username,
                Email = p.Email,
                Roles = string.Join(",", p.RoleNames),
                EmailConfirmed = p.EmailConfirmed.ToString()
            });

            return userListVm;
        }

In ASP.Net core 2.1 we to setup ApplicationRole like this in order to get Roles of users. You need to defined data you want explicit expose for user to use

public class ApplicationRole : IdentityRole
    {
        public virtual ICollection<IdentityUserRole<string>> Users { get; set; }

        public virtual ICollection<IdentityRoleClaim<string>> Claims { get; set; }
    }

Finally

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            foreach (var relationship in modelBuilder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
            {
                relationship.DeleteBehavior = DeleteBehavior.Restrict;
            }

            modelBuilder.Entity<User>().HasMany(u => u.Claims).WithOne().HasForeignKey(c => c.UserId).IsRequired().OnDelete(DeleteBehavior.Cascade);
            modelBuilder.Entity<User>().HasMany(u => u.Roles).WithOne().HasForeignKey(r => r.UserId).IsRequired().OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<ApplicationRole>().HasMany(r => r.Claims).WithOne().HasForeignKey(c => c.RoleId).IsRequired().OnDelete(DeleteBehavior.Cascade);
            modelBuilder.Entity<ApplicationRole>().HasMany(r => r.Users).WithOne().HasForeignKey(r => r.RoleId).IsRequired().OnDelete(DeleteBehavior.Cascade);

            modelBuilder.EnableAutoHistory(null);
        }

The result will be the user name and user roles. If user have more than 1 roles the data will display like this Admin, Editor, etc...

Full code can be found here here here and here Hope this help.

UPDATE: this solution worked with EF Core 5, but seems like it was never supposed to, and it's not possible in EF Core 6 anymore.

You can use EF Core 5.0 Many-To-Many feature, and avoid subclassing IdentityUserRole/IdentityRole.

ApplicationUser

using System.Collections.Generic;
using Microsoft.AspNetCore.Identity;

public class ApplicationUser : IdentityUser
{
    public ICollection<IdentityRole> Roles { get; set; }
}

DbContext:

using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    ...

    protected override void OnModelCreating(ModelBuilder builder)
    {
            base.OnModelCreating(builder);

            builder.Entity<ApplicationUser>()
                .HasMany(u => u.Roles)
                .WithMany("Users")
                .UsingEntity<IdentityUserRole<string>>(
                    userRole => userRole.HasOne<IdentityRole>()
                        .WithMany()
                        .HasForeignKey(ur => ur.RoleId)
                        .IsRequired(),
                    userRole => userRole.HasOne<ApplicationUser>()
                        .WithMany()
                        .HasForeignKey(ur => ur.UserId)
                        .IsRequired());
    }
}

Since this is the top google search result; Nowadays you can just join off the UserRoles dbset (if your db context inherits from IdentityDbContext).

E.g outer joining the roles table to any user roles and then creating our manageUserModel (reduced info of applicationUser class for our api):

var employees = (from bb in _appContext.Users
            join roleIds in _appContext.UserRoles on bb.Id equals roleIds.UserId
            join role in _appContext.Roles on roleIds.RoleId equals role.Id into roles
            orderby bb.LastName, bb.FirstName
            where roles !=null && roles.Any(e => e.Name == Permissions.RoleNames.Administrator || e.Name == Permissions.RoleNames.Employee)
            select ManageUserModel.FromInfo(bb, roles)).ToList();

public static ManageUserModel FromInfo(ApplicationUser info, IEnumerable<UserRole> roles)
    {
        var ret= FromInfo(info);
        ret.Roles = roles.Select(e => new SimpleEntityString() {Id=e.Id, Text=e.Name}).ToList();
        return ret;
    }

This also demos a where clause using any of the role info (the above selects only users in our Admin and Employee roles).

Note: this inner joins the IdentityUserRole, so only users with a role will be returned, if you want all users just add a "into identRoles" to the end of the join roleIds... line and modify the rest of the conditions accordingly.

I implemented a solution to this problem providing a balance between performance and complexity I was happy with. We perform a handful of database roundtrips, one for every role, rather than one for every user. No DbMigrations or class overrides required.

        //Fetch all the Users
        var users = await userManager.Users
            .Select(u => new { User = u, Roles = new List<string>() })
            .ToListAsync();

        //Fetch all the Roles
        var roleNames = await roleManager.Roles.Select(r => r.Name).ToListAsync();

        foreach (var roleName in roleNames)
        {
            //For each role, fetch the users
            var usersInRole = await userManager.GetUsersInRoleAsync(roleName);

            //Populate the roles for each user in memory
            var toUpdate = users.Where(u => usersInRole.Any(ur => ur.Id == u.User.Id));
            foreach (var user in toUpdate)
            {
                user.Roles.Add(roleName);
            }
        }

The accepted answer required customization of identity by extension, which without this will disable the use of roleManager and userManager. When you are customizing ASP.NET Core Identity, you should not use AddEntityFrameworkStores anymore. Because it will override all of your previous settings and customization to default Identity services. First you need to create new services with the following signatures: https://stackoverflow.com/questions/39548668/why-this-violates-the-constraint-of-type-parameter-tuser

Without extending, using userManager and roleManager:

namespace identityDemo.Controllers
{
    public class UserManagementController : Controller
    {
        private readonly ApplicationDbContext _context;
        private readonly RoleManager<IdentityRole> _roleManager;
        private readonly UserManager<IdentityUser> _userManager;

            public UserManagementController(ApplicationDbContext context, 
UserManager<IdentityUser> userManager, RoleManager<IdentityRole> roleManager)
        {
            _context = context;
            _roleManager = roleManager; 
            _userManager = userManager; 
        }

        // GET: ApplicationUserRoles
        public async Task<IActionResult> GetApplicationUsersAndRoles()
        {
            return View(new UserMv(
                (from user in await _userManager.Users.ToListAsync()
                 select new UserMv(user, GetUserRoles(user).Result)).ToList()));
        }

        private async Task<List<string>> GetUserRoles(IdentityUser user)
        {
            return new List<string>(await _userManager.GetRolesAsync(user));
        }
}

With simple constructor for mapping to DTO:

namespace IdentityDemo.Models.ModelView
{
    public class UserMv
    {
public UserMv(IdentityUser aus, List<string> userRoles)
        {
            UserId = aus.Id;
            UserName = aus.UserName;
            RolesHeld = userRoles; 
            Email = aus.Email;
            EmailConfirmed = aus.EmailConfirmed;
            LockoutEnabled = aus.LockoutEnabled;
            AccessFailedCount = aus.AccessFailedCount;
        }
}

and the startup.cs

services.AddDefaultIdentity<IdentityUser>()
.AddRoles<IdentityRole>()
.AddEntityFrameworkStores<ApplicationDbContext>();

ASP.NET CORE 3.1 UPDATE

I use the following code and it works perfectly

  namespace MyProject.Pages.Roles
{
    public class DetailsModel : PageModel
    {

        public UserManager<ApplicationUser> _userManager;
        public RoleManager<IdentityRole> _roleManager;
        private readonly ApplicationDbContext _context;

        public DetailsModel(UserManager<ApplicationUser> userManager,
            RoleManager<IdentityRole> roleManager,
            ApplicationDbContext context)
        {
            _userManager = userManager;
            _roleManager = roleManager;
            _context = context;
        }

        public IList<IdentityRole> Roles { get; set; }

        [BindProperty]
        public IList<ApplicationUser> applicationUserList { get; set; }

        [BindProperty]
        public IList<IdentityRole> allRolesList { get; set; }

        public IList<IdentityUserRole<string>> usersRoles { get; set; }
        public IList<IdentityUserRole<string>> usersRole { get; set; }
        public IList<IdentityUserRole<string>> userWithRole { get; set; }


        public Dictionary<ApplicationUser, string> itemDictionary;

        public async Task<IActionResult> OnGetAsync(string id)
        {
            if (id == null)
            {
                return NotFound();
            }


            Roles = await _context.Roles.Where(r => r.Id == id).ToListAsync();

            allRolesList = await _context.Roles.ToListAsync();

            usersRoles = await _context.UserRoles.ToListAsync();
            usersRole = await _context.UserRoles.Where(r => r.RoleId == id).ToListAsync();
            userWithRole = usersRoles.Where(u => u.RoleId == id).ToList();

            applicationUserList = await _context.Users.ToListAsync();

            itemDictionary = new Dictionary<ApplicationUser, string> { };

            foreach (var item in usersRole)
            {
                itemDictionary.Add(await _context.Users.FindAsync(id = item.UserId), item.UserId);
            }

            return Page();
        }
    }
}

It's very useful to bind all that stuff to get an idea what's going on!

On the Details Razor Page I simply have

    @page "{id}"
@model MyProject.Pages.Roles.DetailsModel
@{
    Layout = "~/Views/Shared/_Layout.cshtml";
    var dict = Model.itemDictionary;
    int cou = dict.Count();
    var x = Model.applicationUserList;
}

<h5 class="bg-primary text-white text-center p-2">List of Members having the role @Model.Roles[0].Name</h5>
<table class="table">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.userWithRole[0].UserId)</th>
            <th>@Html.DisplayNameFor(model => model.userWithRole[0].RoleId)</th>
            <th>LastName, FirstName</th>
        </tr>
    </thead>

    <tbody>

        @foreach (var kvp in dict.ToArray())
        {
            <tr>
                <td>@kvp.Key</td>
                <td>@kvp.Value</td>
                <td>@kvp.Key.LastName, @kvp.Key.FirstName</td>
            </tr>
        }

    </tbody>
</table>

And here the result:

Worked perfectly. I'm using integer keys, so I replaced the "string" with "int"

ApplicationRole : IdentityRole<int>
ApplicationUserRole : IdentityUserRole<int>
ApplicationUser : IdentityUser<int>

ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, int, 
IdentityUserClaim<int>,
ApplicationUserRole, IdentityUserLogin<int>, IdentityRoleClaim<int>, 
IdentityUserToken<int>>

Linq: RoleId = (from a in m.UserRoles select a.Role.Id).FirstOrDefault(),

There is a useful article on microsoft docs https://docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-5.0

For me, exposing navigation properties (roles, users) looked like this(NET 5):

public class ApplicationUser : IdentityUser
{
	public virtual ICollection<IdentityUserClaim<string>> Claims { get; set; }
	public virtual ICollection<IdentityUserLogin<string>> Logins { get; set; }
	public virtual ICollection<IdentityUserToken<string>> Tokens { get; set; }
	public virtual ICollection<ApplicationUserRole> UserRoles { get; set; }
}

public class ApplicationRole : IdentityRole
{
	public virtual ICollection<ApplicationUserRole> UserRoles { get; set; }
}

public class ApplicationUserRole : IdentityUserRole<string>
{
	public virtual ApplicationUser User { get; set; }
	public virtual ApplicationRole Role { get; set; }
}

public class ApplicationDbContext
: IdentityDbContext<
	ApplicationUser, ApplicationRole, string,
	IdentityUserClaim<string>, ApplicationUserRole, IdentityUserLogin<string>,
	IdentityRoleClaim<string>, IdentityUserToken<string>>
{
	public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
		: base(options)
	{
	}

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		base.OnModelCreating(modelBuilder);

		modelBuilder.Entity<ApplicationUser>(b =>
		{
			// Each User can have many UserClaims
			b.HasMany(e => e.Claims)
				.WithOne()
				.HasForeignKey(uc => uc.UserId)
				.IsRequired();

			// Each User can have many UserLogins
			b.HasMany(e => e.Logins)
				.WithOne()
				.HasForeignKey(ul => ul.UserId)
				.IsRequired();

			// Each User can have many UserTokens
			b.HasMany(e => e.Tokens)
				.WithOne()
				.HasForeignKey(ut => ut.UserId)
				.IsRequired();

			// Each User can have many entries in the UserRole join table
			b.HasMany(e => e.UserRoles)
				.WithOne(e => e.User)
				.HasForeignKey(ur => ur.UserId)
				.IsRequired();
		});

		modelBuilder.Entity<ApplicationRole>(b =>
		{
			// Each Role can have many entries in the UserRole join table
			b.HasMany(e => e.UserRoles)
				.WithOne(e => e.Role)
				.HasForeignKey(ur => ur.RoleId)
				.IsRequired();
		});

	}
}

Note that in ApplicationDbContext you can change the primary key type (string in my case)

As @Dreamescaper and @graycrow says you could use shadow many-to-many navigation in EF Core 5.0 even though it should not work.

https://github.com/dotnet/efcore/issues/25383#issuecomment-894785144

https://github.com/dotnet/efcore/issues/23362

Support might be added in EF Core 7.0 with unidirectional many-to-many relationships through shadow navigations again but not completed yet:

https://github.com/dotnet/efcore/issues/3864

I got it working like this using EF Core 6.0:

ApplicationUser:

public class ApplicationUser : IdentityUser
{
    public ICollection<ApplicationRole> Roles { get; set; }
}

ApplicationRole:

public class ApplicationRole : IdentityRole
{
    public ICollection<ApplicationUser> Users { get; set; }

}

Program.cs or Startup.cs:

services.AddDefaultIdentity<ApplicationUser>(options =>
options.SignIn.RequireConfirmedAccount = false)
    .AddRoles<ApplicationRole>()
    .AddEntityFrameworkStores<ApplicationDbContext>();

ApplicationApiAuthorizationDbContext:

//Based on Microsoft.AspNetCore.ApiAuthorization.IdentityServer.ApiAuthorizationDbContext, Version=6.0.2.0
//https://github.com/dotnet/aspnetcore/issues/14161#issuecomment-533468760
public class ApplicationApiAuthorizationDbContext<TUser, TRole> : IdentityDbContext<TUser, TRole, string>, IPersistedGrantDbContext, IDisposable where TUser : IdentityUser where TRole : IdentityRole
{
    private readonly IOptions<OperationalStoreOptions> _operationalStoreOptions;

    public DbSet<PersistedGrant> PersistedGrants
    {
        get;
        set;
    }

    public DbSet<DeviceFlowCodes> DeviceFlowCodes
    {
        get;
        set;
    }

    public DbSet<Key> Keys
    {
        get;
        set;
    }

    public ApplicationApiAuthorizationDbContext(DbContextOptions options, IOptions<OperationalStoreOptions> operationalStoreOptions)
        : base(options)
    {
        _operationalStoreOptions = operationalStoreOptions;
    }

    Task<int> IPersistedGrantDbContext.SaveChangesAsync()
    {
        return base.SaveChangesAsync();
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        builder.ConfigurePersistedGrantContext(_operationalStoreOptions.Value);
    }
}

ApplicationDbContext inherits from ApplicationApiAuthorizationDbContext<ApplicationUser, ApplicationRole> instead of ApiAuthorizationDbContext<ApplicationUser>

public class ApplicationDbContext : ApplicationApiAuthorizationDbContext<ApplicationUser, ApplicationRole>

modelBuilder.Entity<ApplicationUser>()
    .HasMany(u => u.Roles)
    .WithMany(r => r.Users)
    .UsingEntity<IdentityUserRole<string>>(
        userRole => userRole.HasOne<ApplicationRole>()
            .WithMany()
            .HasForeignKey(ur => ur.RoleId)
            .IsRequired(),
        userRole => userRole.HasOne<ApplicationUser>()
            .WithMany()
            .HasForeignKey(ur => ur.UserId)
            .IsRequired());

You can then get all users with roles like this:

var usersWithRoles = dbContext.Users.Include(x => x.Roles).ToList();

I needed to display all the roles a user has a in a view, instead of the solutions provided here already, i went with this quick and dirty thing:

@foreach(var user in Model.Users)
        {
        <tr>
            <td>@user.Email</td>
            <td>@String.Join(", ", @Model._userManager.GetRolesAsync(user).GetAwaiter().GetResult().ToArray())</td>
        </tr>
        }

_userManager has to be public for this to work. and user is simply an instance of IdentityUser.

I solved this by creating a view with all the columns I needed (including roles) and adding it to the context.