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

asp.net-core asp.net-core-identity c# entity-framework-core mysql

Question

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''

1
32
6/23/2018 10:09:13 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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.

62
3/25/2019 9:54:59 PM

Popular Answer

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

Mohamed Aboughoufa

Related Questions

ASP.Net Core how to get the user role in EF Core and Identity

Unfortunately, there's not navigation properties on ...UserRole..., so it's a manual affair. You'd need to do something like:...var userRoleIds = user.Roles.Select(r => r.RoleId); var roles = db.Roles.Where(r => userRoleIds.Contains(r.Id)); ...Then, you can map those on to the user via a view model. For example:...public class UserViewModel { ....
asp.net-core asp.net-core-webapi c# entity-framework-core
asked by Chris Pratt

Retrieving multiple users including their roles with EF Core using ASP.NET Identity

I trying to retrieve multiple users, including their role(s). This is used in a forum, where I want to show the role(s) of users who commented on the thread. I've seen many examples of retrieving the roles for a single ...IdentityUser... using the ...UserManager... and ...RoleManager..., but as mentioned, I have multiple users I wish to retrieve...
asp.net-core asp.net-identity c# entity-framework-core
asked by Detilium

Is it possible/advisable to seed Users/Roles using the EFCore 2.1 Data Seeding system?

As of EFCore 2.1 it's possible to seed data using the DbContext OnModelCreating method....https://docs.microsoft.com/en-us/ef/core/modeling/data-seeding...However user/role creation is usually handled by a UserManager/RoleManager....Now, I can manually create a Role or User - but for example in the case of a User I need to hash the password myself ...
asp.net-core entity-framework-core
asked by David Kirkpatrick

.NET Core 2.1 and EF Framework 2.1

I'm trying to replace a web service that provides information to a mobile app via ODATA 4. The current application is using EF6 and works without error....I have subsequently built a NEW .NET Core 2.1.1, using EF 2.1 and Core Odata 7.0.1. Everything seems to be functioning property when run on IISExpress through the Visual Studio 2017 interface. ..
.net-core c# entity-framework entity-framework-core odata
asked by Stewart Basterash

.Net Core 2.1 EF Core Null Reference Exception when searching database with Where linq query

I have a pretty simple query here:...public async Task<IActionResult> Index(string sortOrder) { ApplicationUser user = await _userManager.GetUserAsync(User); var context = _context.Engine .Include(e => e.EngineType) .Where(e => e.Company == user.Company); return View(await context.ToListAsync());...
.net-core entity-framework-core
asked by Ian Gleeson

Best way to coordinate atomic transactions between different business services in .NET Core / EF Core 2.1?

Let's say you have a UsersService, a TenantsService, and a TenantUserRolesService. ...Each of these services has a "create" method to create their entity and has the same DbContext dependency injected, except for UsersService which has the Identity UserManager class injected....Your goal is to provide an API endpoint that lets a new tenant register...
.net asp.net-core c# entity-framework entity-framework-core
asked by Suren

.NET Core 2.1 Identity : Creating a table for each Role + bridge M:M table

What you do is completely up to your requirements. What you currently have implemented is called ...Table-Per-Hierarchy.... This is the default approach, that Entity Framework does, when discovering its model(s)....An alternative approach would be ...Table-Per-Type.... In this case, Entity Framework would create 4 tables. ...The User table...The St...
.net-core asp.net-core-identity entity-framework-core
asked by Marco

Getting acces to List<T> in model in ASP .Net Core MVC 2.1

I want to do an simply ASP .Net Core MVC APP. ...I have two models:...public class Farming { [Key] public int Id { get; set; } public string Localization { get; set; } public List<Animal> Animals { get; set; } //i want to get into it public List<Worker> Workers { get; set; } //from cshtml file...
asp.net c# entity-framework-core razor
asked by michasaucer

How i can create custom user and role with ASP.NET Core identity v preview 3.0?

When i'm creating migrates, i get error, something like this:...System.InvalidOperationException: Cannot create a DbSet for 'IdentityRole' because this type is not included in the model for the context. System.InvalidOperationException: Cannot create a DbSet for 'UserApp' because this type is not included in the model for the context. ...Yeah, i d...
asp.net-core asp.net-core-identity c# entity-framework-core identity
asked by Vitaliy Zayarniy

Net Core Identity one role per user

I want to have only one ...MyIdentityRole... per ...MyIdentityUser.... My Current Code:...public class MyIdentityUser: IdentityUser { public string FirstName { get; set; } public string LastName { get; set; } public string Image { get; set; } public string PersonalNumber { get; set; } public string Address { get; set; } publ...
.net-core asp.net-core-identity c# entity-framework-core
asked by George Human

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow