Custom Identity User class with one-to-many relationship creates extra column in database

Question

I'm using ASP.NET Core Identity along with Entity Framework Core on .NET 5, using a code first approach with a postgresql database.

I am trying to extend the identity classes like this

public class User : IdentityUser<int>
{
    public ICollection<UserLogin> Logins { get; set; }
}

public class UserLogin : IdentityUserLogin<int>
{
    public User User { get; set; }
}

public sealed class AppDbContext : IdentityDbContext<User, Role, int, UserClaim,UserRole, UserLogin, RoleClaim, UserToken>
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }
}

However, when I run migrations, the tables that it produces look like this -

Notice that an extra column UserId1 has been created.

I would expect it to recognize that the UserLogin.User navigation's Id should be correspond to the IdentityUserLogin.UserId property (according to the conventions laid out by MS here: https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key).

I have also tried overriding IdentityUserLogin.UserId but without luck:

public class UserLogin : IdentityUserLogin<int>
{
    public User User { get; set; }
    public override int UserId { get; set; }
}

Any help would be greatly appreciated. I know that I could probably do a workaround like specifying the mapping manually for these tables, but Id rather figure out how to use the migration tool along with my extended identity classes.

Answer 1

1. Your UserLogin model has a UserId property inherited from IdentityUserLogin.

2. According to the default Identity data model, there is already a one-to-many relationship between IdentityUser and IdentityUserLogin, and the UserId property in IdentityUserLogin serves as the foreign key for this relationship.

3. The Identity database is generated based on the default data model and any extension you make to those models. Therefore, the AspNetUserLogins table already has a UserId column serving as a foreign key to AspNetUsers table.

4. It is up to you whether or not you want navigation properties for the already existing relationships.

Now, you have added navigation properties, but you didn't tell EF that you want these navigations to base on the existing relationship. Therefore, EF is considering this as a new one-to-many relation and creating a new foreign key column UserId1 in AspNetUserLogins table.

In the OnModelCreating method just tell EF that your added navigations should base on the already existing foreign key -

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<User>(e =>
    {
        e.HasMany(p => p.Logins)
        .WithOne(p => p.User)
        .HasForeignKey(p => p.UserId);  // inherited from IdentityUserLogin
    });
}

Answer 2

IdentityUserLogin already has a UserId property which will turn into UserId columnn IdentityUserLogin SourceCode

When you add the User property to UserLogin, EF will also add a UserId property (Like you pointed out in your question). And that is then turned into a column usually called UserId

In order to prevent duplicate property names Ef has to make your UserId different by adding the suffix 1