Entity Framework Core : invalid column name 'UserId1'

Question

I am trying to use Entity Framework Core / .NET 5 to interact with my databases.

When I try to query DbContent.UserClaims I get the following error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'UserId1'.

I am not sure where UserId1 us coming from. I have a property called UserId which is the foreign key. Here are the relation mapping

Here is what I tried to do in the DbContext class

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

    builder.Entity<User>(user =>
    {
        user.HasKey(r => r.Id);
        user.HasMany(x => x.UserRoles).WithOne().HasForeignKey(x => x.UserId);
        user.HasMany(x => x.UserClaims).WithOne().HasForeignKey(x => x.UserId);
        user.HasMany(x => x.UserTokens).WithOne().HasForeignKey(x => x.UserId);
    });

    builder.Entity<UserClaim>(userClaim =>
    {
        userClaim.HasKey(r => r.Id);
        userClaim.HasOne(r => r.User).WithOne().HasForeignKey<UserClaim>(x => x.UserId);
    });
}

Here is the UserClaim class which is derived from IdentityUserClaim

public class UserClaim : IdentityUserClaim<string>
{
    public virtual User User { get; set; }
}

Here is the User class which is derived from IdentityUser

public class User : IdentityUser<string>
{
    public virtual ICollection<UserToken> UserTokens { get; set; }
    public virtual ICollection<UserRole> UserRoles { get; set; }
    public virtual ICollection<UserClaim> UserClaims { get; set; }
}

Here is the query that EF5 is generating

SELECT [u].[Id], [u].[ClaimType], [u].[ClaimValue],[u].[UserId], [u].[UserId1]
FROM [UserClaims] AS [u]

How can I fix this issue in Entity Framework Core?

Answer 1

You're using shadow properties here, and on top of that, trying to add UserId foreign key to the User itself. Since UserId is an already defined property in that class, it's adding a suffix to the property name every time you're trying to add a foreign key in the user table by the same name.

It should be something like this:

modelBuilder.Entity<UserClaim>()
        .Property<int>("UserForeignKey");

modelBuilder.Entity<UserClaim>()
    .HasOne(a => a.User)
    .WithMany(b => b.UserClaims)
    .HasForeignKey("UserForeignKey")

Read the documentation on how to configure Fluent API for shadow properties, and some other ways to use the Fluent API.