EF Core 3.1 creates duplicate column with '1' in name when defining Principal Key relationship between entities

Viewed 1212

I have a very simple test database in SQL Server with two tables, "Cakes" and "Flavors." One flavor can correspond to many cakes. Each Cake record has a nullable "FlavorType" column which corresponds to the "FlavorType" column in the Flavors table which is NOT the primary key for the Flavors table.

When I try to model this relationship in EF Core, when my data context is being built, it creates an additional "FlavorType1" shadow property on my Cake entity. And then when I attempt to hit my very simple OData service (with the equivalent to "SELECT * FROM Cakes") the query fails because it includes this nonexistent FlavorType1 column in the list of columns to select.

If I rename the "FlavorType" column on "Cakes" to something else, like "FType", then the EF Core data context is built without issue and my query runs successfully. This problem only occurs if the principal key column of Flavors and the related column of Cakes have the same name. Any suggestions for how to get around this? This situation happens frequently on a work project I'm looking at, and I can't ensure that the principal key column and the related entity's column must have different names.

Here's a screenshot of tables and columns in database. And here's the Cake.cs model:

[Table("Cakes")]
public class Cake
{
    [Key]
    public int CakeUniqueIdentifier { get; set; }

    public string Description { get; set; }

    public string FlavorType { get; set; }

    public Flavor Flavor { get; set; }
}

Flavor.cs model:

[Table("Flavors")]
public class Flavor
{
    public Flavor()
    {
        Cakes = new HashSet<Cake>();
    }

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

    public string FlavorType { get; set; }

    public string Description { get; set; }

    public IEnumerable<Cake> Cakes { get; set; }
}

Data context:

public class MyDataContext : DbContext, IDataContext
{
    public MyDataContext(DbContextOptions options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Cake>()
            .HasKey("CakeUniqueIdentifier");

        modelBuilder.Entity<Cake>()
            .HasOne(t => t.Flavor)
            .WithMany(s => s.Cakes)
            .HasPrincipalKey("FlavorType");
    }
}

Query that fails:

    Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (156ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [c].[CakeUniqueIdentifier], [c].[Description], [c].[FlavorType], [c].[FlavorType1]
FROM [Cakes] AS [c]

Debug view of the model that's built when running the app:

  EntityType: Cake
    Properties: 
      CakeUniqueIdentifier (int) Required PK AfterSave:Throw ValueGenerated.OnAdd
        Annotations: 
          SqlServer:ValueGenerationStrategy: IdentityColumn
          TypeMapping: Microsoft.EntityFrameworkCore.Storage.IntTypeMapping
      Description (string)
        Annotations: 
          TypeMapping: Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping
      FlavorType (string)
        Annotations: 
          TypeMapping: Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping
      FlavorType1 (no field, string) Shadow FK Index
        Annotations: 
          TypeMapping: Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping
    Navigations: 
      Flavor (Flavor) ToPrincipal Flavor Inverse: Cakes
    Keys: 
      CakeUniqueIdentifier PK
    Foreign keys: 
      Cake {'FlavorType1'} -> Flavor {'FlavorType'} ToDependent: Cakes ToPrincipal: Flavor
    Annotations: 
      ConstructorBinding: Microsoft.EntityFrameworkCore.Metadata.ConstructorBinding
      Relational:TableName: Cakes
  EntityType: Flavor
    Properties: 
      Id (int) Required PK AfterSave:Throw ValueGenerated.OnAdd
        Annotations: 
          SqlServer:ValueGenerationStrategy: IdentityColumn
          TypeMapping: Microsoft.EntityFrameworkCore.Storage.IntTypeMapping
      Description (string)
        Annotations: 
          TypeMapping: Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping
      FlavorType (string) Required AlternateKey AfterSave:Throw
        Annotations: 
          TypeMapping: Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping
    Navigations: 
      Cakes (IEnumerable<Cake>) Collection ToDependent Cake Inverse: Flavor
    Keys: 
      FlavorType
      Id PK
    Annotations: 
      ConstructorBinding: Microsoft.EntityFrameworkCore.Metadata.ConstructorBinding
      Relational:TableName: Flavors
Annotations: 
  ProductVersion: 3.1.3
  Relational:MaxIdentifierLength: 128
  SqlServer:ValueGenerationStrategy: IdentityColumn```


2 Answers

Oops... found what was going on. I had to add this to the Fluent configuration: .HasForeignKey("FlavorType") and now the model is built as expected.

modelBuilder.Entity<Cake>()
            .HasOne(t => t.Flavor)
            .WithMany(s => s.Cakes)
            .HasPrincipalKey("FlavorType")
            .HasForeignKey("FlavorType");

I've been in a similar trouble, duplicated columns in select... In my case, it turns out that the fix was :

// wrong
builder.HasOne<UserAccount>().WithMany().HasForeignKey(c => c.UserAccountId);

// OK
builder.HasOne(x => x.UserAccount).WithMany().HasForeignKey(c => c.UserAccountId);
Related