Duplicate foreign keys and columns being created for an id EF Core 3.1

Question

Got a issue with ef core 3.1 creating duplicate columns for a id field from another table. i currently have a ApplicationUser entity that inherits from IdentityUser, and a property entity that stores an ApplicationUser id as UserId.

 public class Property
    {
        public Guid PropertyId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public Guid AddressId { get; set; }
        public Address PropertyAddress { get; set; }
        public bool IsHome { get; set; }
        public string UserId { get; set; }
        public ApplicationUser User { get; set; }
    }

public class ApplicationUser : IdentityUser
    {
        public IEnumerable<Property> properties { get; set; }
    }

public class PropertyConfig : IEntityTypeConfiguration<Property>
    {
        public void Configure(EntityTypeBuilder<Property> builder)
        {
            builder.HasKey(p => p.PropertyId);
            builder.HasOne(p => p.PropertyAddress).WithOne();
            builder.HasOne(p => p.User).WithMany(p => p.properties).HasForeignKey(f => f.UserId).OnDelete(DeleteBehavior.Restrict);
        }
    }

 public class ApplicationUserConfig : IEntityTypeConfiguration<ApplicationUser>
    {
        public void Configure(EntityTypeBuilder<ApplicationUser> builder)
        {
            builder.ToTable("User");
            builder.HasKey(p => p.Id);
            builder.HasMany<Property>().WithOne(p => p.User).HasForeignKey(f => f.UserId);
        }
    }

Above are my classes and i have run the migration which produces this table for property.

migrationBuilder.CreateTable(
                name: "Property",
                columns: table => new
                {
                    PropertyId = table.Column<Guid>(nullable: false),
                    Name = table.Column<string>(nullable: true),
                    Description = table.Column<string>(nullable: true),
                    AddressId = table.Column<Guid>(nullable: false),
                    IsHome = table.Column<bool>(nullable: false),
                    UserId = table.Column<string>(nullable: true),
                    ApplicationUserId = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Property", x => x.PropertyId);
                    table.ForeignKey(
                        name: "FK_Property_Address_AddressId",
                        column: x => x.AddressId,
                        principalTable: "Address",
                        principalColumn: "AddressId",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_Property_User_ApplicationUserId",
                        column: x => x.ApplicationUserId,
                        principalTable: "User",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_Property_User_UserId",
                        column: x => x.UserId,
                        principalTable: "User",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

As you can see it is creating a UserId column and foreign key which is correct. but its also producing a ApplicationUserId and i cannot figure out what is causing it.

Any Idea?

Any help would be appreciated.

@AluanHaddad thanks that has sorted the issue such a small error for 2 hours of headaches. if you create an answer i will mark it as may be useful for someone else caught out by this. — Adam Wilson, Jul 09 '20 at 16:29

Aluan Haddad · Accepted Answer · 2023-04-14T13:10:03.443

In your fluent API configuration

public void Configure(EntityTypeBuilder<ApplicationUser> builder) {
    builder.HasMany<Property>()
        .WithOne(p => p.User)
        .HasForeignKey(f => f.UserId);
}

You are simply saying that there is a one-to-many relationship between the ApplicationUser and Property entities, but not which members are involved from the many side. Indeed there may be multiple associations between Property and ApplicationUser.

To get it working, specify the members involved on both ends by adjusting your configuration like so

public void Configure(EntityTypeBuilder<ApplicationUser> builder) {
    builder.HasMany(u => u.properties)
        .WithOne(p => p.User)
        .HasForeignKey(f => f.UserId);
}

score 0 · Answer 2 · answered Jul 09 '20 at 16:07

It's because you have both:

  public string UserId { get; set; }
  public ApplicationUser User { get; set; }

If you don't wan't User to reference the User table you'll have to ignore it. Something like this (I can't remember if it's the exact syntax)

   builder.Property(p => p.User).Ignore();

But that means that when you fetch Property from the context User will be null. Also if you attach an ApplicationUser to Property and then save the property - neither the User nor the relantioship will be written to the database, and you'll need to set UserId manually.

but from my understanding this should be allowed as show here : https://www.learnentityframeworkcore.com/conventions/one-to-many-relationship#fully-defined-relationship — Adam Wilson, Jul 09 '20 at 16:11

Duplicate foreign keys and columns being created for an id EF Core 3.1

2 Answers2

Linked