UserRole table has duplicate but differently named columns, causes role checking issues

Question

My project makes use of Microsoft ASP.NET Identity Framework and until recently was using version 2.0 and working properly. I noticed back then that the UserRole table had 4 columns holding essentially the same data:

Ever since upgrading to 2.1, it seems to leave the second set [Null] - which is a good thing as far as I am concerned (I cannot find where those extra columns have been defined.)

modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaim");
        modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogin");
        modelBuilder.Entity<IdentityRole>().ToTable("Role");
        modelBuilder.Entity<ApplicationUser>().ToTable("User");

        modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
        modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
        modelBuilder.Entity<IdentityUserRole>().HasKey(r => new
        {
            r.RoleId, 
            r.UserId
        }).ToTable("UserRole");;

The issue arises when I go to use it:

await _manager.AddToRoleAsync(applicationUser.Id, "Admin");

calling this populates the first two columns, whereas:

var userRoles = await _manager.GetRolesAsync(applicationUser.Id);

seems to query the second set of columns. I know this because they return [Null] unless I copy and paste the values across from the first two columns.

I have been looking all over my code for anything that may shed some light on this but I will admit to being at a complete loss at this point.

Answer 1

After some judicious searching, I found this Authorize and GetRoles doesn't work in ASP.NET Identity post. This combined with some editing of the migration solved the problem.

To Summarize:

I added

 base.OnModelCreating(modelBuilder);

to my context file and edited the model generation portion

        modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaim");
        modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogin");
        modelBuilder.Entity<IdentityRole>().ToTable("Role");
        modelBuilder.Entity<ApplicationUser>().ToTable("User");
        modelBuilder.Entity<IdentityUserRole>().ToTable("UserRole");

        //modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
        //modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
        //modelBuilder.Entity<ApplicationUser>().HasMany<IdentityUserRole>(u => u.Roles);
        //modelBuilder.Entity<IdentityUserRole>().HasKey(r => new
        //{
        //    r.RoleId, 
        //    r.UserId
        //});

Your migration code tries to delete some columns and rename others, so instead of delete and rename, I had it delete the columns it was going to rename and kept the pre-existing columns. To drop the pre-existing columns, you will have to remove the Foreign keys as well. I also had to prevent it from re-creating primary keys.

        DropIndex("dbo.UserClaim", new[] { "ApplicationUser_Id" });
        DropIndex("dbo.UserLogin", new[] { "ApplicationUser_Id" });
        DropIndex("dbo.UserRole", new[] { "ApplicationUser_Id" });
        DropIndex("dbo.UserRole", new[] { "IdentityRole_Id" });
        DropForeignKey("FK_dbo_UserClaim_ApplicationUser_Id", "ApplicationUser_Id");
        DropForeignKey("FK_dbo_UserLogin_ApplicationUser_Id", "ApplicationUser_Id");
        DropForeignKey("FK_dbo_UserRole_ApplicationUser_Id", "ApplicationUser_Id");
        DropForeignKey("FK_dbo_UserRole_IdentityRole_Id", "Identity_Id");
        DropColumn("dbo.UserClaim", "ApplicationUser_Id");
        DropColumn("dbo.UserLogin", "ApplicationUser_Id");
        DropColumn("dbo.UserRole", "ApplicationUser_Id");
        DropColumn("dbo.UserRole", "IdentityRole_Id");
        //DropPrimaryKey("dbo.UserLogin");
        //DropPrimaryKey("dbo.UserRole");
        AlterColumn("dbo.User", "Email", c => c.String(maxLength: 256));
        AlterColumn("dbo.User", "UserName", c => c.String(nullable: false, maxLength: 256));
        AlterColumn("dbo.UserClaim", "UserId", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.UserClaim", "UserId", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.UserLogin", "LoginProvider", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.UserLogin", "ProviderKey", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.UserLogin", "UserId", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.UserRole", "UserId", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.UserRole", "RoleId", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.Role", "Name", c => c.String(nullable: false, maxLength: 256));
        //AddPrimaryKey("dbo.UserLogin", new[] { "LoginProvider", "ProviderKey", "UserId" });
        //AddPrimaryKey("dbo.UserRole", new[] { "UserId", "RoleId" });
        CreateIndex("dbo.User", "UserName", unique: true, name: "UserNameIndex");
        CreateIndex("dbo.UserClaim", "UserId");
        CreateIndex("dbo.UserLogin", "UserId");
        CreateIndex("dbo.UserRole", "UserId");
        CreateIndex("dbo.UserRole", "RoleId");
        CreateIndex("dbo.Role", "Name", unique: true, name: "RoleNameIndex");

This was a frustrating experience with Microsoft ASP.NEt Identity and I hope the above helps someone keep a few more strands of hair.