EF Core 2 duplicate column created with foreign key relationship

Question

I'm trying add migration using EF core 2 code first method. The issue is that, the entities with foreign key relationship are created with a foreign key id suffixed with '1' at the end and a redundant column with the same name but without the 1 at the end which is not a foreign key.

Examples are my 2 classes, Store and StoreVisit as shown below:

Store

[Table("Store")]
public class Store
{
    public Store()
    {
        StoreVisits = new HashSet<StoreVisit>();
    }
    [Key]
    public int StoreId { get; set; }

    [StringLength(30)] 
    public string ShopName { get; set; }

    [StringLength(50)]
    public string ShopKeeper { get; set; } 

    public string ContactNo { get; set; }

    [StringLength(70)]
    public string Address { get; set; }

    [StringLength(20)]
    public string Street { get; set; }

    [StringLength(50)] 
    public string City { get; set; }

    public IEnumerable<StoreVisit> StoreVisits { get; set; }
}

Store Visit

[Table("StoreVisit")]
public class StoreVisit
{

    [Key]
    public int StoreVisitId { get; set; }

    [StringLength(50)]
    public string Location { get; set; }

    [StringLength(50)]
    public string Notes { get; set; }

    [DataType(DataType.Time)]
    public DateTime StartTime { get; set; }

    [DataType(DataType.Time)]
    public DateTime EndTime { get; set; }

    public Store Store { get; set; }

}

The Visit class is created in the database with the column shown in the image below:

As you can see, the StoreVisit table has columns "StoreId1" which is the actual foreign key and "StoreId" which is not a foreign key.

I have even configured the relationship with Fluent API as below:

            modelBuilder.Entity<Store>()
            .HasMany(c => c.StoreVisits)
            .WithOne(e => e.Store)
            .IsRequired();

Can someone help.

Answer 1

Note that Entity Framework Core is smart enough to detect relationships among your classes which will be turned into database tables with relationships if you use its conventions. So this is redundant to use annotations like [Key] above StoreId property.

Second thing, As an advice, try to use simple and clean names for classes or properties as they can be potentially similar to those automatically created by EF. For example, in your case I prefer to avoid using store inside StoreVisit class name again (e.g in case of many to many relationship, derived table is named StoreVisit like one that you employed just without 's', Although your case is one to many),

And Final tip is the reason for appearing redundant StoreId column. Actually, In your case, this is not necessary to use Fluent API as EF can detect the relationship. In addition, you've written wrong configuration for modelBuilder. So remove it and let EF to generate it (unless you plan to have fully defined relationship to consume its advantages in your code).

• The StoreId is one that you told EF to generate it (as required) in modelBuilder.
• The StoreId1 is EF Auto generated column (Foreign Key) based on one to many relationship. '1' is appended in order to avoid column name duplication.

Answer 2

A foreign key needs to be defined on the class.

[Table("StoreVisit")]
public class StoreVisit
{

    [Key]
    public int StoreVisitId { get; set; }

    public int StoreId { get; set; }

    [StringLength(50)]
    public string Location { get; set; }

    [StringLength(50)]
    public string Notes { get; set; }

    [DataType(DataType.Time)]
    public DateTime StartTime { get; set; }

    [DataType(DataType.Time)]
    public DateTime EndTime { get; set; }

    public Store Store { get; set; }

}

It also would hurt to add the foreign key reference to the Fluent API.

modelBuilder.Entity<Store>()
            .HasMany(c => c.StoreVisits)
            .WithOne(e => e.Store)
            .HasForeignKey(e => e.StoreId)
            .IsRequired();