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?

nebula · Accepted Answer · 2021-09-30 23:32:53Z

4

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.

edited Sep 30, 2021 at 23:32

answered Sep 30, 2021 at 23:25

nebula

934 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Morgeth888 Over a year ago

What in the world possessed the developer to think that was ok? Throw an error don't just assume there is a *1, *2 column.

Natrium Over a year ago

Thank you for pointing out that this is called "shadow properties". It made searching for an explanation a lot easier!

Benjamin Freitag · Accepted Answer · 2023-06-05 15:45:04Z

2

Identity model customization has different samples, take care of this difference:

b.HasMany(e => e.UserRoles).WithOne()            // may cause invalid column name 'userid1'
b.HasMany(e => e.UserRoles).WithOne(e => e.User) // works

answered Jun 5, 2023 at 15:45

Benjamin Freitag

6336 silver badges8 bronze badges

Collectives™ on Stack Overflow

Entity Framework Core : invalid column name 'UserId1'

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related