EF Core 8 database first with Identity User tables

Question

I already have a database which contains identity user tables (AspNetUser, AspRole,...). I am using DB first to generate Models, I've configured every necessary services in program.cs. The project ran well until I start using services (ex: UserManager<AppUser>) related to Identity. It caused a lot of exceptions (un-countable). I asked AI and googled a lot but seems the hope has lost.

Here is all the models were generated: Entity Models

Program.cs configuration: Configuration

Here are some of the exceptions, i found a post that had the same issue but did not work for me: View detail here

1. cannot use table 'appuserroles' for entity type 'appuserrole' since it is being used for entity type 'appuserrole (dictionary<string, object>)' and potentially other entity types, but there is no linking relationship. add a foreign key to 'appuserrole' on the primary key properties and pointing to the primary key on another entity type mapped to 'appuserroles'.

2. Npgsql.PostgresException (0x80004005): 42P01: relation "AppUser" does not exist.

3. System.InvalidOperationException: Cannot use table 'AppRoleClaims' for entity type 'IdentityRoleClaim<Guid>' since it is being used for entity type 'AppRoleClaim' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'IdentityRoleClaim<Guid>' on the primary key properties and pointing to the primary key on another entity type mapped to 'AppRoleClaims'.

Here is my Database Context:

public partial class MyUserContext : IdentityDbContext<AspNetUser, IdentityRole<Guid>, Guid>
{
    public MyUserContext()
    {
    }

    public MyUserContext(DbContextOptions<MyUserContext> options)
        : base(options)
    {
    }

    public virtual DbSet<AspNetRole> AspNetRoles { get; set; }
    public virtual DbSet<AspNetRoleClaim> AspNetRoleClaims { get; set; }
    public virtual DbSet<AspNetUser> AspNetUsers { get; set; }
    public virtual DbSet<AspNetUserClaim> AspNetUserClaims { get; set; }
    public virtual DbSet<AspNetUserLogin> AspNetUserLogins { get; set; }
    public virtual DbSet<AspNetUserToken> AspNetUserTokens { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer("server=(local);database=MyUser;Trusted_Connection=True;TrustServerCertificate=True;uid=sa;pwd=T@n12345678901;");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AspNetRole>(entity =>
        {
            entity.HasIndex(e => e.NormalizedName, "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<AspNetRoleClaim>(entity =>
        {
            entity.HasIndex(e => e.RoleId, "IX_AspNetRoleClaims_RoleId");
            entity.HasOne(d => d.Role).WithMany(p => p.AspNetRoleClaims).HasForeignKey(d => d.RoleId);
        });

        modelBuilder.Entity<AspNetUser>(entity =>
        {
            entity.HasIndex(e => e.NormalizedEmail, "EmailIndex");
            entity.HasIndex(e => e.NormalizedUserName, "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);

            entity.HasMany(d => d.Roles).WithMany(p => p.Users)
                .UsingEntity<Dictionary<string, object>>(
                    "AspNetUserRole",
                    r => r.HasOne<AspNetRole>().WithMany().HasForeignKey("RoleId"),
                    l => l.HasOne<AspNetUser>().WithMany().HasForeignKey("UserId"),
                    j =>
                    {
                        j.HasKey("UserId", "RoleId");
                        j.ToTable("AspNetUserRoles");
                        j.HasIndex(new[] { "RoleId" }, "IX_AspNetUserRoles_RoleId");
                    });
        });

        modelBuilder.Entity<AspNetUserClaim>(entity =>
        {
            entity.HasIndex(e => e.UserId, "IX_AspNetUserClaims_UserId");
            entity.HasOne(d => d.User).WithMany(p => p.AspNetUserClaims).HasForeignKey(d => d.UserId);
        });

        modelBuilder.Entity<AspNetUserLogin>(entity =>
        {
            entity.HasKey(e => new { e.LoginProvider, e.ProviderKey });
            entity.HasIndex(e => e.UserId, "IX_AspNetUserLogins_UserId");
            entity.HasOne(d => d.User).WithMany(p => p.AspNetUserLogins).HasForeignKey(d => d.UserId);
        });

        modelBuilder.Entity<AspNetUserToken>(entity =>
        {
            entity.HasKey(e => new { e.UserId, e.LoginProvider, e.Name });
            entity.HasOne(d => d.User).WithMany(p => p.AspNetUserTokens).HasForeignKey(d => d.UserId);
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Answer 1

After randomly configured in the OnModelCreating(), i finally resolved all exceptions.

1. Map all the identity classes to Database tables (map to table's name)

modelBuilder.Entity<IdentityRole<Guid>>().ToTable("AspNetRoles"); modelBuilder.Entity<IdentityUserRole<Guid>>().ToTable("AspNetUserRoles"); modelBuilder.Entity<IdentityUserLogin<Guid>>().ToTable("AspNetUserLogins"); modelBuilder.Entity<IdentityUserToken<Guid>>().ToTable("AspNetTokens"); modelBuilder.Entity<IdentityUserClaim<Guid>>().ToTable("AspNetUserTokens"); modelBuilder.Entity<AspNetUser>().ToTable("AspNetUsers"); // This line is important, remove it will cause the 2nd exception (I don't know why)

2. Map the generated classes (AspNetRole, AspNetUserToken,...) to non-exsited table name. This is the most essential to avoid Identity and AspNet refer to the same table and cause the 3rd exceptions. Ex: modelBuilder.Entity<AspNetRole>().ToTable("random table name here")