Entity Framework Core migration issue

Question

Having an issue with a many to many relationship in EF Core 3.1.16.

Pretty basic concept of: user / user_role / role.

Using MS Identity so have a dependency on IdentityUserRole class. Since that class has UserId and RoleId properties on them already, EF is trying to generate those fields by convention. I haven't figured out a way to tell EF to treat the MS UserId and RoleId fields as the same fields as the User.Id and Role.Id fields from the objects that I want on the UserRole entity class. We use IEntityTypeConfiguration instances to create the database schema that EF uses, rather than convention based approach. The code to generate the schema is below.

USER

    public class User : IdentityUser<int>, IEntityCustom
    {
        // Custom field for testing purpose only...wouldn't actually store this.
        public int Age { get; set; }

        public List<UserRole> UserRoles { get; set; }
    }


    public class UserTypeConfiguration : BaseTypeConfiguration<User>, IEntityTypeConfiguration<User>
    {
        public UserTypeConfiguration() : base("permissions", "user") { }

        public override void Configure(EntityTypeBuilder<User> builder)
        {
            base.Configure(builder);

            #region "Hiding other properties"
            // Other fields are here that I omitted as they are just lowercasing only
            // and dont have relation to what is going on in the question.   user_name
            // field below is same as all the code omitted just with that field name pair.
            #endregion
            builder.Property(x => x.UserName).HasColumnName("user_name");


            builder.HasMany(x => x.UserRoles).WithOne(x => x.User).HasForeignKey("user_id");



            builder.HasData(new User
            {
                Id = 1,
                AccessFailedCount = 0,
                Age = 12,
                ConcurrencyStamp = Guid.NewGuid().ToString(),
                Email = "test@yopmail.com",
                EmailConfirmed = true,
                LockoutEnabled = false,
                NormalizedEmail = "test@yopmail.com",
                NormalizedUserName = "test",
                PasswordHash = "hash",
                PhoneNumber = "5551231234",
                PhoneNumberConfirmed = true,
                SecurityStamp = Guid.NewGuid().ToString(),
                TwoFactorEnabled = false,
                UserName = "test"
            });
        }
    }

USER_ROLE

    public class UserRole : IdentityUserRole<int>, IEntityCustom
    {
        public int Id { get; set; }

        public User User { get; set; }
        public Role Role { get; set; }


        public UserRole() { }

        public UserRole(User user, Role role)
        {
            User = user;
            Role = role;
        }
    }


    public class UserRoleTypeConfiguration : BaseTypeConfiguration<UserRole>, IEntityTypeConfiguration<UserRole>
    {
        public UserRoleTypeConfiguration() : base("permissions", "user_role") { }

        public override void Configure(EntityTypeBuilder<UserRole> builder)
        {
            base.Configure(builder);


            // Gens the right schema, but fails at runtime on 
            // insert to user_role table with duplicate "role_id" column exists on table "user_role"
            builder.Property<int>("UserId").HasColumnName("user_id");
            builder.Property<int>("RoleId").HasColumnName("role_id");

            // These 2 lines of code seem dumb, but they fix the schema
            // Only way I could find to accomplish it tho...
            builder.Property<int>("user_id").HasColumnName("user_id");
            builder.Property<int>("role_id").HasColumnName("role_id");

            builder.HasOne(x => x.User).WithMany(x => x.UserRoles).HasForeignKey("user_id");
            builder.HasOne(x => x.Role).WithMany(x => x.UserRoles).HasForeignKey("role_id");


            #region Region with all the ways I tried that didnt work.
            // Removed all the stuff that I tried that didn't get the model right.
            // ie....duplicate fields, etc.
            #endregion
        }
    }

ROLE

    public class Role : IdentityRole<int>, IEntityCustom
    {
        // Another custom field for testing.  eg.  Meaning = "SYSTEMADMIN"
        public string Meaning { get; set; }


        // I really dont want this navigation and my real code doesnt have it currently.
        // I couldnt get it working without, so I finally just added to see if I could get it 
        // working as full many to many relation and would remove later.
        public List<UserRole> UserRoles { get; set; }
    }



    public class RoleTypeConfiguration : BaseTypeConfiguration<Role>, IEntityTypeConfiguration<Role>
    {
        public RoleTypeConfiguration() : base("permissions", "role") { }

        public override void Configure(EntityTypeBuilder<Role> builder)
        {
            base.Configure(builder);

            builder.Property(x => x.Name).HasColumnName("name");
            builder.Property(x => x.ConcurrencyStamp).HasColumnName("concurrency_stamp");
            builder.Property(x => x.NormalizedName).HasColumnName("normalized_name");
            builder.Property(x => x.Meaning).HasColumnName("meaning");



            builder.HasMany(x => x.UserRoles).WithOne(x => x.Role).HasForeignKey("role_id");



            builder.HasData(new Role
            {
                Id = 1,
                ConcurrencyStamp = Guid.NewGuid().ToString(),
                Meaning = "SYSADMIN",
                Name = "System Admin",
                NormalizedName = "system admin"
            });


            builder.HasData(new Role
            {
                Id = 2,
                ConcurrencyStamp = Guid.NewGuid().ToString(),
                Meaning = "CONTENTADMIN",
                Name = "Content Admin",
                NormalizedName = "content admin"
            });
        }
    }

BASE TYPE CONFIGURATION

    public class BaseTypeConfiguration<TEntity> : IEntityTypeConfiguration<TEntity> where TEntity : class, IEntityCustom
    {
        private readonly string _schemaName;
        private readonly string _tableName;

        public BaseTypeConfiguration(string schemaName, string tableName)
        {
            _schemaName = schemaName;
            _tableName = tableName;
        }

        public virtual void Configure(EntityTypeBuilder<TEntity> builder)
        {
            Console.WriteLine("schema:" + _schemaName);
            Console.WriteLine("table:" + _tableName);

            builder.Metadata.SetSchema(_schemaName);
            builder.ToTable(_tableName);

            builder.HasKey(x => x.Id);

            builder.Property(x => x.Id)
                .HasColumnName("id")
                .ValueGeneratedOnAdd();
        }
    }

IENTITYCUSTOM

public interface IEntityCustom
    {
        int Id { get; set; }
    }

OUTPUT

In the output section below, is what the migration process creates. This examples generates the right fields on the model for user_role (ie...single user_id and role_id fields, coupled with the core id field), but when you look at the FK refs it generates, both fields are duplicated. I tried running this scenario, and it does run the migration and will create the tables correctly, but when you try to run an insert against the user_role table, EF throws the following exception:

"42701: column role_id specified more than once."

migrationBuilder.CreateTable(
                name: "user_role",
                schema: "permissions",
                columns: table => new
                {
                    id = table.Column<int>(nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    user_id = table.Column<int>(nullable: false),
                    role_id = table.Column<int>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_user_role", x => x.id);
                    table.ForeignKey(
                        name: "FK_user_role_role_role_id",
                        column: x => x.role_id,
                        principalSchema: "permissions",
                        principalTable: "role",
                        principalColumn: "id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_user_role_user_user_id",
                        column: x => x.user_id,
                        principalSchema: "permissions",
                        principalTable: "user",
                        principalColumn: "id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_user_role_role_role_id1",
                        column: x => x.role_id,
                        principalSchema: "permissions",
                        principalTable: "role",
                        principalColumn: "id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_user_role_user_user_id1",
                        column: x => x.user_id,
                        principalSchema: "permissions",
                        principalTable: "user",
                        principalColumn: "id",
                        onDelete: ReferentialAction.Cascade);
                });

CREATED DATABASE

Answer 1

You should try to configure your entities like this:

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasMany(x => x.UserRoles)
            .WithOne(x => x.User)
            .HasForeignKey(x => x.UserId);
    }
}

public class RoleConfiguration : IEntityTypeConfiguration<Role>
{
    public void Configure(EntityTypeBuilder<Role> builder)
    {
        builder.HasMany(x => x.UserRoles)
            .WithOne(x => x.Role)
            .HasForeignKey(x => x.RoleId);
    }
}

public void Configure(EntityTypeBuilder<UserRole> builder)
{
    builder.Property(x => x.RoleId).HasColumnName("role_id");
    builder.HasKey(x => new {x.UserId, x.RoleId});
}

When you specify foreign keys, you should use model properties instead of specifying the names like "role_id" or "user_id". Since you defined that property as e.g. "role_id" in your table, EF will handle all the other thing internally and will map your foreign keys correctly. But in the example you provided, EF can't understand to which poperty it should map, that's why it generates additional columns.

You should also consider using this for renaming all your properties on the database level: Naming Conventions for Entity Framework Core Tables and Columns.

The Id field in your UserRole class is also redundant, since you only need a composite key {UserId, RoleId}