EF Core 8 database first with Identity User tables

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);
}

If you have based your code on an example, they are not using an explicit entity for tables like AspNetUserRole, so you should not try and declare one. These tables are joining tables for many-to-many relationships and are optional in many cases.

To fix your issue, remove the entity declarations for these joining entities like AspNetUserRole. Your mappings are already catering for the joining table.

For instance your code here:

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");
                });

This is declaring the relationship between the User and Role to the AspNetUserRoles table but mapping it as a basic Dictionary rather than an explicit entity class. The explicit class is not needed, but you could adjust the mapping to use it. In either case you don't need DbSets declared for many-to-many joining tables, simply let EF work these out where your entities should be using User.Roles and Role.Users respectively.