如何在Entity Framework Core 6中覆盖外键列名称映射？

使用EF Core 6连接SQL Server，现有三张表：order、orderitem、product。order主键为id，orderitem的外键是orderid和product_id。实体配置看似正确，但EF Core生成的查询始终用ProductId关联product表，而非product_id，导致报错「Invalid column name 'ProductId'」。

错误SQL片段 #

FROM [ShipEngineOrderItems] AS [s0]   
LEFT JOIN [Products] AS [p] ON [s0].[ProductId] = [p].[Id]

完整报错信息 #

Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (90ms) [Parameters=[@__orderId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [s].[id], [s].[order_date], [t].[PId], [t].[ProductName], [t].[Price], [t].[id], [t].[Id0]
FROM [ShipEngineOrders] AS [s]
LEFT JOIN (
SELECT [s0].[product_id] AS [PId], [p].[WarehouseSku] AS [ProductName], [s0].[price] AS [Price], [s0].[id], [p].[Id] AS [Id0], [s0].[orderId]
FROM [ShipEngineOrderItems] AS [s0]
LEFT JOIN [Products] AS [p] ON [s0].[ProductId] = [p].[Id]
) AS [t] ON [s].[id] = [t].[orderId]
WHERE [s].[id] = @__orderId_0
ORDER BY [s].[id], [t].[id]

fail: Microsoft.EntityFrameworkCore.Query[10100]

An exception occurred while iterating over the results of a query for context type 'Core.Data.ApplicationDbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ProductId'.

相关代码 #

实体类代码 #

public partial class Order
{
    public int id { get; set; }
    public DateTime order_date { get; set; }
    public virtual ICollection<OrderItem> OrderItems { get; set; }
}

public partial class OrderItem
{
    public int id { get; set; }
    [Column(TypeName = "decimal(18,2")]
    public decimal price { get; set; }
    public int orderId { get; set; } // navigation property
    public virtual Order Order { get; set; }
    [Column("product_id")]
    public int product_id { get; set; } // navigation property
    public virtual Product Product { get; set; }
}

public partial class Product
{
   public int Id { get; set; }
   public string? WarehouseSku { get; set; } 
}

DbContext代码 #

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() { }
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : 
    base(options) { }

    public virtual DbSet<Order> Order { get; set; }
    public virtual DbSet<OrderItem> OrderItem { get; set; }
    public virtual DbSet<Product> Product { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
           .ToTable("ShipEngineOrders", t => t.ExcludeFromMigrations());

        //modelBuilder.Entity<OrderItem>()
        //    .Property(i => i.product_id).HasColumnName("product_id");

        modelBuilder.Entity<OrderItem>()
           .ToTable("ShipEngineOrderItems", t => t.ExcludeFromMigrations());
        
        modelBuilder.Entity<Product>()
            .ToTable("Products", t => t.ExcludeFromMigrations());
    }
}

修正方案（两种可选） #

方案1：使用Fluent API明确配置外键（推荐） #

在ApplicationDbContext的OnModelCreating方法中，为OrderItem与Product的关联指定外键字段，这是最清晰且不易冲突的方式：

modelBuilder.Entity<OrderItem>()
    .ToTable("ShipEngineOrderItems", t => t.ExcludeFromMigrations())
    .HasOne(oi => oi.Product)
    .WithMany() // 若Product无反向导航属性，直接使用WithMany()
    .HasForeignKey(oi => oi.product_id); // 指定外键为product_id字段

方案2：修正数据注解 #

为Product导航属性添加[ForeignKey]注解，明确关联到product_id字段：

[Column("product_id")]
public int product_id { get; set; } 

[ForeignKey("product_id")]
public virtual Product Product { get; set; }

原因说明 #

EF Core的默认约定会将导航属性名（Product）拼接Id作为默认外键列名（ProductId）。即使给product_id字段添加了[Column]注解，若未明确关联导航属性与外键字段，EF Core仍会遵循默认约定生成关联条件，导致错误。

内容的提问来源于stack exchange，提问作者Tony Wang