Entity Framework同表外键关联Link表引发SQL列名错误排查

数据库表结构 #

• Component表：主键为ComponentId，用于定义数据流组件的属性
• ComponentLink表：复合主键为(FromComponentId, ToComponentId)，用于关联组件间的链接关系

实体类代码 #

Component实体类 #

[Table("Component")]
public partial class Component
{
    [Key]
    public int ComponentId { get; set; }

    public byte ComponentType { get; set; }

    [Required]
    [StringLength(100)]
    [Unicode(false)]
    public string Name { get; set; }

    public int PackageId { get; set; }

    public int? OffsetX { get; set; }

    public int? OffsetY { get; set; }

    [InverseProperty("Component")]
    public virtual ICollection<Column> Columns { get; set; } = new List<Column>();

    [InverseProperty("FromComponent")]
    public virtual ICollection<ComponentLink> ComponentLinkFromComponents { get; set; } = new List<ComponentLink>();

    [InverseProperty("ToComponent")]
    public virtual ICollection<ComponentLink> ComponentLinkToComponents { get; set; } = new List<ComponentLink>();
}

ComponentLink实体类 #

[PrimaryKey("FromComponentId", "ToComponentId")]
[Table("ComponentLink")]
public partial class ComponentLink
{
    [Key]
    public int FromComponentId { get; set; }

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

    public short FromSortIndex { get; set; }

    public short ToSortIndex { get; set; }

    [ForeignKey("FromComponentId")]
    [InverseProperty("ComponentLinkFromComponents")]
    public virtual Component FromComponent { get; set; }

    [ForeignKey("ToComponentId")]
    [InverseProperty("ComponentLinkToComponents")]
    public virtual Component ToComponent { get; set; }
}

DbContext模型配置代码 #

modelBuilder.Entity<Component>(entity =>
{
    entity.HasOne(d => d.Package).WithMany(p => p.Components).HasConstraintName("FK_Component_Package");
});

modelBuilder.Entity<ComponentLink>(entity =>
{
    entity.HasOne(d => d.FromComponent).WithMany(p => p.ComponentLinkFromComponents)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_ComponentLink_FromComponent");

    entity.HasOne(d => d.ToComponent).WithMany(p => p.ComponentLinkToComponents).HasConstraintName("FK_ComponentLink_ToComponent");
});

出错的查询代码 #

var query = db.Components
    .Include(c => c.ComponentLinkToComponents)
    .Include(c => c.ConditionalSplits)
    .Where(x => x.ComponentId == id || x.PackageId == PackageId)
    .ToList();

报错信息 #

Microsoft.Data.SqlClient.SqlException HResult=0x80131904，提示“无效列名 'ComponentId'、无效列名 'ComponentId1'”

尝试过的无效解决方案 #

在ComponentLink的模型配置中显式指定复合主键，但问题依旧：

modelBuilder.Entity<ComponentLink>(entity =>
{
    entity.HasKey(x => new { x.FromComponentId, x.ToComponentId });
    entity.HasOne(d => d.FromComponent).WithMany(p => p.ComponentLinkFromComponents)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_ComponentLink_FromComponent");
   entity.HasOne(d => d.ToComponent).WithMany(p => p.ComponentLinkToComponents).HasConstraintName("FK_ComponentLink_ToComponent");
});

EF生成的错误SQL语句 #

SELECT [c].[ComponentId], [c].[ComponentType], [c].[Name], [c].[OffsetX], [c].[OffsetY], [c].[PackageId], [c0].[FromComponentId], 
[c0].[ToComponentId], [c0].[ComponentId], [c0].[ComponentId1], 
[c0].[FromSortIndex], [c0].[ToSortIndex], [c1].[ComponentId], [c1].[SortIndex], [c1].[Expression]
FROM [Component] AS [c]
LEFT JOIN [ComponentLink] AS [c0] ON [c].[ComponentId] = [c0].[ToComponentId]
LEFT JOIN [ConditionalSplit] AS [c1] ON [c].[ComponentId] = [c1].[ComponentId]
WHERE [c].[PackageId] = @__PackageId_1   
ORDER BY [c].[ComponentId], [c0].[FromComponentId], [c0].[ToComponentId], [c1].[ComponentId]

问题分析与修复方案 #

从生成的SQL可以看出，EF Core错误地尝试查询ComponentLink表中不存在的ComponentId和ComponentId1列，根源是ComponentLink实体类的主键配置冲突：
实体类同时使用了[PrimaryKey]类特性和[Key]字段特性，导致EF Core混淆了主键定义，错误认为该实体除复合主键外还有额外的主键字段。

修复步骤（二选一即可） #

方案一：保留类级别的[PrimaryKey]特性，移除字段上的[Key]特性

[PrimaryKey("FromComponentId", "ToComponentId")]
[Table("ComponentLink")]
public partial class ComponentLink
{
    public int FromComponentId { get; set; }

    public int ToComponentId { get; set; }

    public short FromSortIndex { get; set; }

    public short ToSortIndex { get; set; }

    [ForeignKey("FromComponentId")]
    [InverseProperty("ComponentLinkFromComponents")]
    public virtual Component FromComponent { get; set; }

    [ForeignKey("ToComponentId")]
    [InverseProperty("ComponentLinkToComponents")]
    public virtual Component ToComponent { get; set; }
}

方案二：移除类级别的[PrimaryKey]特性，保留字段上的[Key]特性（EF Core会将多个[Key]标记识别为复合主键）

[Table("ComponentLink")]
public partial class ComponentLink
{
    [Key]
    public int FromComponentId { get; set; }

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

    public short FromSortIndex { get; set; }

    public short ToSortIndex { get; set; }

    [ForeignKey("FromComponentId")]
    [InverseProperty("ComponentLinkFromComponents")]
    public virtual Component FromComponent { get; set; }

    [ForeignKey("ToComponentId")]
    [InverseProperty("ComponentLinkToComponents")]
    public virtual Component ToComponent { get; set; }
}

额外检查项 #

如果修复后问题仍存在，请确认：

• 数据库中的ComponentLink表确实不存在ComponentId和ComponentId1列，实体类与数据库表结构完全匹配
• 清理项目并重新构建，确保EF Core的模型元数据被正确刷新

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