Can't Extract Users List With Roles From ASP.NET IdentityUserRole

Question

I am trying to get user list with their co responding Roles. Using IdentityUser, IdentityRole, IdentityUserRole. I have already tried different way and followed many Answer from StackOverflow. The main issue I have, when I add Navigation property to User and Role models then in UserRole table duplicate some columns.

Please let me know if you need more informations

But I need your help.

Details: User:

    public class User : IdentityUser
    {
        public string Gender { get; set; }
        public ICollection<UserRole> UserRoles { get; set; }
    }

Role:

    public class Role : IdentityRole<string>
    {
        public ICollection<UserRole> UserRoles { get; set; }
    }

UserRole:

    public class UserRole : IdentityUserRole<string>
    {
        public User User { get; set; }
        public Role Role { get; set; }
    }

DbContext:

    public class DataContext : IdentityDbContext<User, Role, string,  IdentityUserClaim<string>,
    UserRole, IdentityUserLogin<string>, IdentityRoleClaim<string>, IdentityUserToken<string>>
    {
        public DataContext(DbContextOptions<DataContext> options) : base(options) {}

    }

After Migration and Updating database: Table AspNetUserRoles:

ps: Those data inserted before.

I getting frustrated, Please help

poke poke · Accepted Answer · 2020-07-19T12:00:37

public class UserRole : IdentityUserRole<string>
{
    public User User { get; set; }
    public Role Role { get; set; }
}

The base type IdentityUserRole<> already has a UserId and a RoleId. By adding new properties for User and Role, without configuring the context to account for those, you are essentially introducing new properties into the table (UserId1 and RoleId1) which are never populated.

You should be able to fix this by reconfiguring the context. The default configuration looks like this:

builder.Entity<TUser>(b =>
{
    b.HasMany<TUserRole>().WithOne().HasForeignKey(ur => ur.UserId).IsRequired();
});

builder.Entity<TRole>(b =>
{
    b.HasMany<TUserRole>().WithOne().HasForeignKey(ur => ur.RoleId).IsRequired();
});

So if you overwrite those relationship configurations, you should be able to make your navigation properties work:

public class DataContext : IdentityDbContext<User, Role, string,  IdentityUserClaim<string>, UserRole, IdentityUserLogin<string>, IdentityRoleClaim<string>, IdentityUserToken<string>>
{
    public DataContext(DbContextOptions<DataContext> options)
        : base(options) {}

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

        builder.Entity<User>(b =>
        {
            b.HasMany<UserRole>(u => u.UserRoles)
                .WithOne(ur => ur.User)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();
        });

        builder.Entity<Role>(b =>
        {
            b.HasMany<UserRole>(r => r.UserRoles)
                .WithOne(ur => ur.Role)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();
        });
    }
}

I haven’t tested this but it should replace the default configuration, so that you shouldn’t end up with duplicate columns on the UserRole and your navigation properties should work then.

Can't Extract Users List With Roles From ASP.NET IdentityUserRole

1 Answers