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

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.

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.

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"