EF Core插入AlertRule时提示‘RuleBaseId’无效列名问题排查

问题场景 #

现有两个多对一关联的模型类，执行插入操作时EF Core生成的SQL包含未定义的RuleBaseId列，导致报错：

SqlException: Invalid column name 'RuleBaseId'

相关代码 #

模型类

public class AlertRule: Identity
{
    public long RuleId { get; set; }

    [ForeignKey(nameof(RuleId))]
    public virtual SepRuleBase Rule { get; set; }

    public long AlertId { get; set; }

    [ForeignKey(nameof(AlertId))]
    public virtual Alert Alert { get; set; }
}

public abstract class SepRuleBase : BaseEntity
{
    public string Name { get; set; }
    public int Score { get; set; }
    public string Description { get; set; }

    [JsonIgnore]
    public bool IsDeleted { get; set; }

    [JsonIgnore]
    public bool IsActive { get; set; } = true;

    [JsonIgnore]
    public int Type { get; set; }

    public virtual ICollection<AlertRule> AlertRules { get; set; }

    public override string GetTableName()
    {
        return "SepRule";
    }
}

public abstract class Identity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }
}

public abstract class BaseEntity: Identity
{
    [JsonIgnore]
    public DateTime LastUpdate { get; set; }
    [JsonIgnore]
    public string LastUpdateBy { get; set; }

    public abstract string GetTableName();
}

配置类

public class AlertRuleConfiguration: IEntityTypeConfiguration<AlertRule>
{
    public void Configure(EntityTypeBuilder<AlertRule> builder)
    {
        builder.ToTable("AlertRule".ConvertDbName())
            .HasKey(x => x.Id);

        builder.Property(x => x.Id)
            .HasColumnName("ID".ConvertDbName())
            .UseOracleIdentityColumn()
            .IsRequired();

        builder.Property(x => x.AlertId)
            .HasColumnName("AlertId".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.RuleId)
            .HasColumnName("RuleId".ConvertDbName())
            .IsRequired();

        builder
            .HasOne(p => p.Rule)
            .WithMany(p => p.AlertRules)
            .HasForeignKey(p => p.RuleId)
            .HasConstraintName("FK_AlertRule_Rule".ConvertDbName())
            .OnDelete(DeleteBehavior.Cascade);
        
        builder
            .HasOne(p => p.Alert)
            .WithMany(p => p.AlertRules)
            .HasForeignKey(p => p.AlertId)
            .HasConstraintName("FK_AlertRule".ConvertDbName())
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasIndex(p => p.AlertId).HasName("IX_AlrtRl_AlrtId".ConvertDbName());
        builder.HasIndex(p => p.RuleId).HasName("IX_AlrtRl_RlId".ConvertDbName());
    }
}

public class SepRuleBaseConfiguration : IEntityTypeConfiguration<SepRuleBase>
{
    public void Configure(EntityTypeBuilder<SepRuleBase> builder)
    {
        builder.HasKey(x => x.Id);

        builder.Property(x => x.Id)
            .HasColumnName("ID".ConvertDbName())
            .UseSqlServerIdentityColumn() 
            .IsRequired();

        builder.Property(x => x.Type)
            .HasColumnName("Type".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.Name)
            .HasColumnName("Name".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.Score)
            .HasColumnName("Score".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.Description)
            .HasColumnName("Description".ConvertDbName())
            .IsRequired(false);

        builder.Property(x => x.LastUpdate)
            .HasColumnName("LastUpdate".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.LastUpdateBy)
            .HasColumnName("LastUpdateBy".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.IsDeleted)
            .HasColumnName("IsDeleted".ConvertDbName())
            .IsRequired();

        builder.Property(x => x.IsActive)
            .HasColumnName("IsActive".ConvertDbName())
            .IsRequired();

        builder.ToTable("SepRule".ConvertDbName());
    }
}

插入代码

_dbContext.AlertRules.Add(new AlertRule
{
    AlertId = dbAlert.Id,
    RuleId = long.Parse(rule)
});
_dbContext.SaveChanges();

生成的错误SQL

Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (54ms) [Parameters=[@p0='35', @p1='' (DbType = Int64), @p2='1'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [ALERTRULE] ([ALERTID], [RuleBaseId], [RULEID])
      VALUES (@p0, @p1, @p2);
      SELECT [ID]
      FROM [ALERTRULE]
      WHERE @@ROWCOUNT = 1 AND [ID] = scope_identity();

问题原因 #

1. 数据库提供者配置冲突：AlertRule的ID列使用了Oracle专属的UseOracleIdentityColumn()配置，但生成的SQL使用了SQL Server的scope_identity()，说明实际数据库是SQL Server。这种跨数据库的配置会导致EF Core实体元数据解析异常，进而错误生成默认外键列RuleBaseId。
2. 配置类未正确加载：如果DbContext的OnModelCreating方法中未手动应用配置类，EF Core会回退到默认约定，对抽象类SepRuleBase的导航属性生成默认外键列RuleBaseId，而非使用代码中定义的RuleId。

解决方案 #

1. 统一数据库身份列配置 #

将AlertRuleConfiguration中的Oracle身份列配置替换为SQL Server版本：

builder.Property(x => x.Id)
    .HasColumnName("ID".ConvertDbName())
    .UseSqlServerIdentityColumn() // 替换为SQL Server身份列配置
    .IsRequired();

2. 确保配置类被正确加载 #

在DbContext的OnModelCreating方法中显式应用所有配置类：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    // 应用AlertRule配置
    modelBuilder.ApplyConfiguration(new AlertRuleConfiguration());
    // 应用SepRuleBase配置
    modelBuilder.ApplyConfiguration(new SepRuleBaseConfiguration());
    // 其他实体配置
}

3. 清理缓存并重新编译 #

删除项目的bin和obj目录，重新编译项目，避免旧的EF Core元数据缓存干扰配置生效。

4. 验证关联配置唯一性 #

检查DbContext中是否存在其他重复的AlertRule与SepRuleBase关联配置，确保仅保留一处明确的外键配置（即使用RuleId作为外键）。

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