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.

Wynne

Something is missing from the picture. Do you have StoreId property in your StoreVisit class? Or some fluent configuration not shown here? – Embrocate

Agreed. EF would not create the StoreId column on its own, because it's a bigint and the PK on Store is just int. Even if there was some issue with your entities/configuration that was causing EF to create the FK property twice, it would be two int columns. The fact that it's a bigint means you're adding that manually somewhere. – Gynaecology

I had this issue. This was down to a glitch in the way I was doing the fluent API cascade delete restrictions. I set up a relationship with an incorrect HasForeignKey , and the end result was that EF started creating duplicate fields. – Illboding

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.

Hogg

I see, I am having the same issue here. Firstly I am creating the navigational properties and respective foreign keys like: Catalog { int CatalogId; List<Item> items }; Item { int ItemId, int CatalogId, Catalog parentCatalog }; With this configured relations are setup fine. Then I am adding the fluent API foreign key relationship for the same as I need Cascading delete behavior. My services rely upon CatalogId field in Item so I cannot remove it after configuring it with fluent api. Now EF Core2 generates the CatalogId and CatalogId1 FKs, both int's and second w/o Cascade. How to fight this? – Abidjan

I found an answer here: stackoverflow.com/a/52957270 . Just use Entity.HasOne with lambda to specify exact element otherwise EF will create new one. – Abidjan

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();

Corwun

The trick here is that the HasForeignKey in the question is missing. – Illboding

I had HasForeignKey but it still ended up creating duplicate foreignKey – Sorn

:) "would hurt" or "would not hurt" ? – Vogeley

Answer 3

    loginPW() {
  return new Promise((resolve, reject) => {
    connection.promise().query('SELECT card_pin FROM card')
      .then(([rows, fields]) => {
        const cardPin = rows[0].card_pin;
        const loginArray = [cardPin];
        resolve(loginArray);
      })
      .catch((error) => {
        reject(error);
      });
  });
}


----------


const mysql = require('mysql2/promise');
const dotenv = require('dotenv');
dotenv.config();
const connection = mysql.createPool(process.env.MYSQL_SERVER);
module.exports = connection;

Caber

A code-only answer is not high quality. While this code may be useful, you can improve it by saying why it works, how it works, when it should be used, and what its limitations are. Please edit your answer to include explanation and link to relevant documentation. – Sidelight