Unable to Save User Accounts After Renaming Identity Tables In Asp.Net MVC Core App

Question

I have managed to customize Identity table names and IdentityUser as described on this forum. My problem is that I'm unable to save user accounts thereafter and I get the error below:

Cannot use table App Users for entity type App Users since it is being used for entity type ApplicationUser and there is no relationship between their primary keys.

I have included herein a demo app that can be downloaded from here

After customizing table names, I did add a migration and updated the database, all these operations where successful, I then used the dotnet ef dbcontext scaffold command to import other database tables. After this, I added another migration which was unsuccessful, ef framework throws the error below:

Cannot use table 'AppRole' for entity type 'AppRole' since it is being used for entity type 'IdentityRole' and there is no relationship between their primary keys.

I have reset the migrations, deleted the _migration table and started all over again but can't get it to work. I therefore beseech you to assist me in this regard.

This is a snapshot of DB-Context

protected override void OnModelCreating(ModelBuilder modelBuilder)

{
    base.OnModelCreating(modelBuilder);

    modelBuilder.HasAnnotation("ProductVersion", "2.2.3-servicing-35854");

    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        var table = entityType.Relational().TableName;
        if (table.StartsWith("AspNet"))
        {
            entityType.Relational().TableName = table.Replace(table.Substring(0, 6), "App");
        }
    };

    modelBuilder.Entity<AppRoleClaims>(entity =>
    {
        entity.HasIndex(e => e.RoleId);

        entity.Property(e => e.RoleId).IsRequired();

        entity.HasOne(d => d.Role)
            .WithMany(p => p.AppRoleClaims)
            .HasForeignKey(d => d.RoleId);
    });

    modelBuilder.Entity<AppRoles>(entity =>
    {
        entity.HasIndex(e => e.NormalizedName)
            .HasName("RoleNameIndex")
            .IsUnique()
            .HasFilter("([NormalizedName] IS NOT NULL)");

        entity.Property(e => e.Id).ValueGeneratedNever();

        entity.Property(e => e.Name).HasMaxLength(256);

        entity.Property(e => e.NormalizedName).HasMaxLength(256);
    });

    modelBuilder.Entity<AppUserClaims>(entity =>
    {
        entity.HasIndex(e => e.UserId);

        entity.Property(e => e.UserId).IsRequired();

        entity.HasOne(d => d.User)
            .WithMany(p => p.AppUserClaims)
            .HasForeignKey(d => d.UserId);
    });

    modelBuilder.Entity<AppUserLogins>(entity =>
    {
        entity.HasKey(e => new { e.LoginProvider, e.ProviderKey });

        entity.HasIndex(e => e.UserId);

        entity.Property(e => e.LoginProvider).HasMaxLength(128);

        entity.Property(e => e.ProviderKey).HasMaxLength(128);

        entity.Property(e => e.UserId).IsRequired();

        entity.HasOne(d => d.User)
            .WithMany(p => p.AppUserLogins)
            .HasForeignKey(d => d.UserId);
    });

    modelBuilder.Entity<AppUserRoles>(entity =>
    {
        entity.HasKey(e => new { e.UserId, e.RoleId });

        entity.HasIndex(e => e.RoleId);

        entity.HasOne(d => d.Role)
            .WithMany(p => p.AppUserRoles)
            .HasForeignKey(d => d.RoleId);

        entity.HasOne(d => d.User)
            .WithMany(p => p.AppUserRoles)
            .HasForeignKey(d => d.UserId);
    });

    modelBuilder.Entity<AppUserTokens>(entity =>
    {
        entity.HasKey(e => new { e.UserId, e.LoginProvider, e.Name });

        entity.Property(e => e.LoginProvider).HasMaxLength(128);

        entity.Property(e => e.Name).HasMaxLength(128);

        entity.HasOne(d => d.User)
            .WithMany(p => p.AppUserTokens)
            .HasForeignKey(d => d.UserId);
    });

    modelBuilder.Entity<AppUsers>(entity =>
    {
        entity.HasIndex(e => e.NormalizedEmail)
            .HasName("EmailIndex");

        entity.HasIndex(e => e.NormalizedUserName)
            .HasName("UserNameIndex")
            .IsUnique()
            .HasFilter("([NormalizedUserName] IS NOT NULL)");

        entity.Property(e => e.Id).ValueGeneratedNever();

        entity.Property(e => e.Email).HasMaxLength(256);

        entity.Property(e => e.NormalizedEmail).HasMaxLength(256);

        entity.Property(e => e.NormalizedUserName).HasMaxLength(256);

        entity.Property(e => e.UserName).HasMaxLength(256);
    });
}

Answer 1

Good day, here is a detailed guideline to anyone who wants to use database first approach with custom Identity tables using Asp.Net Core 2.2 and ef core.

Step 1 ==> Create your database first and add all the tables, relationships, and constraints

step 2 ==> assuming you have added all ef framework dependencies, head over to Visual Studio and create one class called e.g ApplicationUser that inherits from IdentityUser, also add custom fields there.

step 3 ==> configure your DB context class to use ApplicationUser and do the same in Startup.cs when registering AddDbContext service and also use OnModelCreating to customize Identity table names. Remember to invoke base.OnModelCreating(modelBuilder)

step 4 ==> add a migration and update database, this generates identity tables in our already existing DB.

step 5 ==> Head over to your Relational Database Management System (RDBMS) and verify that your customized Identity tables have been added. If so, then go to step 6, else troubleshoot the problem before continuing

step 6 ==> Issue dotnet ef dbcontext scaffold "connectionstring" Microsoft.EntityFrameworkCore.SqlServer -o Models/Entities -f -c "TempDbContext" command from powershell or an equivalent of this using package manager console

step 7 ==> Very Important: look for all Identity table related entities whose names were customized in step 3. Make sure these 7 entities all inherit from Identity specifying their Primary key type for example:

public partial class AppUsers : IdentityUser<string>{}
public partial class AppUserTokens : IdentityUserToken<string>{}
public partial class AppUserRoles : IdentityUserRole<string>{}
public partial class AppUserLogins : IdentityUserLogin<string>{}
public partial class AppUserClaims : IdentityUserClaim<string>{}
public partial class AppRoles : IdentityRole<string>{}
public partial class AppRoleClaims : IdentityRoleClaim<string>{}

After this go to the initial DBContext class, the one registered in ConfigureServices in startup.cs and change the class signature to

public class ApplicationDbContext : IdentityDbContext<AppUsers, AppRoles, string, AppUserClaims, AppUserRoles, AppUserLogins, AppRoleClaims, AppUserTokens>

Step 8: copy all the DbSet and everything method inside OnModelCreating from temporary TempDBContext auto-generated by the scaffold command in step 6 and replace what you have in your initial DBContext or you could alternatively make TempDBContext your main DBContext but remember to update ConfigureServices.

step 9: Update all references to ApplicationUser and make them point to AppUsers, copy all custom fields from ApplicationUser to AppUsers, the reason for doing this is that ApplicationUser doesn't have a reference table to map to in the database but AppUsers does.

step 10 ==> Update all your views and partial views injected with

@inject SignInManager<AppUsers> SignInManager
@inject UserManager<AppUsers> UserManager

to use AppUsers from ApplicationUser especially _LoginPartial.cshtml and you are done. you can now add user Accounts as follows:

var user = new AppUsers
                {
                    Id = Guid.NewGuid().ToString(),
                    UserName = "example@gmail.com",
                    Email = "example@gmail.com",
                    PhoneNumber = "0123456789",
                };

                var result = await userManager.CreateAsync(user, "Ex@mple88@gmail.com");

Done!!! do some code clean-up and delete any files and code no longer needed, this is how I succeeded to make Identity work using Custom Table Names and DB first approach. Failure to follow these steps to the later will result in errors I posted and many others. Thank you for reading through.

Answer 2

You can change the table names using the below:

base.OnModelCreating(builder);

builder.Entity<UserProfile>().ToTable("UserProfile");
builder.Entity<UserProfile>().Property(up => up.Id).HasColumnName("UserId");
builder.Entity<UserRole>().ToTable("Role").Property(ur => ur.Id).HasColumnName("RoleId");
builder.Entity<UserRole>().Property(ur => ur.Id).HasColumnName("UserRoleId");
builder.Entity<IdentityUserClaim<int>>().ToTable("UserClaim");
builder.Entity<IdentityUserRole<int>>().ToTable("UserRole").HasKey(k => new {k.RoleId, k.UserId});
builder.Entity<IdentityUserLogin<int>>().ToTable("UserLogin");
builder.Entity<IdentityRoleClaim<int>>().ToTable("RoleClaim");
builder.Entity<IdentityUserToken<int>>().ToTable("UserToken");

Ref: https://mitchelsellers.com/blogs/2017/09/05/taking-control-of-aspnet-identity-table-names