Entity Framework is combining my table name and the Id name

Question

This is an odd issue. My Entity Framework Core project is combining the table and the Id into one column like this: MonsterListMonsterId

Here is the error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'MonsterListMonsterId'

This is the controller that generates the error:

var treasuresByMonster = await _context.MonsterTreasures.Where(q => q.MonsterId == id).ToListAsync();

Clearly the column is called MonsterId. Not MonsterListMonsterId.

Here is the MonsterList class:

public class MonsterList
{
    public MonsterList()
    {
        MonsterTreasures = new HashSet<MonsterTreasures>();
    }
    public Guid MonsterId { get; set; }
    public string MonsterText { get; set; }

    public virtual ICollection<MonsterTreasures> MonsterTreasures { get; set; }
}

Here is the MonsterTreasure class:

public partial class MonsterTreasures
{
    public Guid TreasureId { get; set; }
    public Guid? MonsterId { get; set; }
    public string TreasureText { get; set; }
    public MonsterList MonsterList { get; set; }

}

And the context class:

modelBuilder.Entity<MonsterTreasures>(entity =>
{
    entity.HasKey(e => e.TreasureId);
    
    entity.HasOne(e => e.MonsterList)
        .WithMany(m => m.MonsterTreasures);
        
    entity.ToTable("MonsterTreasures");

    entity.Property(e => e.TreasureId)
        .HasColumnName("TreasureId");

    entity.Property(e => e.MonsterId)
        .HasColumnName("MonsterId");

    entity.Property(e => e.TreasureText)
        .HasColumnName("TreasureText");

});

What is a proper fix for this?

Thanks!

Answer 1

The problem is here

public Guid? MonsterId { get; set; }
public MonsterList MonsterList { get; set; }

The name 'MonsterId` does not match any of the EF Core conventional FK property names:

• <navigation property name><principal key property name>
• <navigation property name>Id
• <principal entity name><principal key property name>
• <principal entity name>Id

Because of that EF Core does not consider it as a FK property and assumes shadow property / column with default name from the exception message.

In order to fix the issue you have to map it explicitly by either [ForeignKey] data annotation on FK property specifying the navigation property name:

[ForeignKey(nameof(MonsterList)]
public Guid? MonsterId { get; set; }
public MonsterList MonsterList { get; set; }

or on navigation property specifying the name of the FK property:

public Guid? MonsterId { get; set; }
[ForeignKey(nameof(MonsterId)]
public MonsterList MonsterList { get; set; }

or with HasForeignKey Fluent API here:

entity.HasOne(e => e.MonsterList)
    .WithMany(m => m.MonsterTreasures)
    .HasForeignKey(e => e.MonsterId); // <--