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 properties { get; set; }
    }

public class PropertyConfig : IEntityTypeConfiguration
    {
        public void Configure(EntityTypeBuilder 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
    {
        public void Configure(EntityTypeBuilder builder)
        {
            builder.ToTable("User");
            builder.HasKey(p => p.Id);
            builder.HasMany().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(nullable: false),
                    Name = table.Column(nullable: true),
                    Description = table.Column(nullable: true),
                    AddressId = table.Column(nullable: false),
                    IsHome = table.Column(nullable: false),
                    UserId = table.Column(nullable: true),
                    ApplicationUserId = table.Column(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.

user_1915893 · Answer

In your fluent API configuration

public void Configure(EntityTypeBuilder builder) {
    builder.HasMany()
        .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 ApplicationUser.

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

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

user_3608449 · Answer

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.

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

2 Answers