EF Core单外键关联两类主键报错：主键不存在于另一表的解决方案

想用单个外键列Invoice_Id搭配InvoiceType字段替代两个可空外键列，但向PurchaseInvoice表插入数据时，出现“主键不存在于SellingInvoice表”的数据库冲突错误。需要在保留导航属性和物理外键关联的前提下解决该问题。

public class PurchaseInvoice
{
    public int Id { get; set; }
    public Int64 InvoiceNumber { get; set; }
    public ICollection<ProductSerial> ProductSerials { get; set; }
}

public class SellingInvoice
{
    public int Id { get; set; }
    public Int64 InvoiceNumber { get; set; }
    public ICollection<ProductSerial> ProductSerials { get; set; }
}

public class ProductSerial
{
    public int Id { get; set; }
    public int Serial_Id { get; set; }
    public int Product_Id { get; set; }
    public int Invoice_Id { get; set; }
    public Enums.InvoiceType InvoiceType { get; set; }

    public PurchaseInvoice? PurchaseInvoice { get; set; }
    public SellingInvoice? SellingInvoice { get; set; }
}

public class PurchaseInvoiceConfig : IEntityTypeConfiguration<PurchaseInvoice>
{
    public void Configure(EntityTypeBuilder<PurchaseInvoice> entity)
    {
        entity.HasMany(q => q.ProductSerials)
              .WithOne(q => q.PurchaseInvoice)
              .IsRequired(false)
              .HasForeignKey(q => q.Invoice_Id)
              .OnDelete(DeleteBehavior.NoAction);
    }
}
           
public class SellingInvoiceConfig : IEntityTypeConfiguration<SellingInvoice>
{
    public void Configure(EntityTypeBuilder<SellingInvoice> entity)
    {
        // 注：原代码中FProductSerials应为笔误，修正为ProductSerials
        entity.HasMany(q => q.ProductSerials)
              .WithOne(q => q.SellingInvoice)
              .IsRequired(false)
              .HasForeignKey(q => q.Invoice_Id)
              .OnDelete(DeleteBehavior.NoAction);
    }
}

问题出在EF Core默认会把Invoice_Id同时作为两个外键，分别关联到PurchaseInvoice和SellingInvoice的主键，但没结合InvoiceType做区分——插入采购发票时，EF会检查这个Invoice_Id是否在销售发票表里存在，直接触发冲突。

要搞定这个问题，得给两个关联关系分别加筛选条件，让EF只在InvoiceType匹配的时候才认这个外键，具体改法如下：

修改PurchaseInvoice配置 #

public class PurchaseInvoiceConfig : IEntityTypeConfiguration<PurchaseInvoice>
{
    public void Configure(EntityTypeBuilder<PurchaseInvoice> entity)
    {
        entity.HasMany(q => q.ProductSerials)
              .WithOne(q => q.PurchaseInvoice)
              .IsRequired(false)
              .HasForeignKey(q => q.Invoice_Id)
              // 只匹配发票类型为采购的ProductSerial记录
              .HasFilter($"{nameof(ProductSerial.InvoiceType)} = {(int)Enums.InvoiceType.Purchase}")
              .OnDelete(DeleteBehavior.NoAction);
    }
}

修改SellingInvoice配置 #

public class SellingInvoiceConfig : IEntityTypeConfiguration<SellingInvoice>
{
    public void Configure(EntityTypeBuilder<SellingInvoice> entity)
    {
        entity.HasMany(q => q.ProductSerials)
              .WithOne(q => q.SellingInvoice)
              .IsRequired(false)
              .HasForeignKey(q => q.Invoice_Id)
              // 只匹配发票类型为销售的ProductSerial记录
              .HasFilter($"{nameof(ProductSerial.InvoiceType)} = {(int)Enums.InvoiceType.Selling}")
              .OnDelete(DeleteBehavior.NoAction);
    }
}

注意事项 #

1. 确保Enums.InvoiceType的枚举数值和数据库中存储的一致，比如Purchase对应1、Selling对应2这类，避免筛选条件失效。
2. 这种配置会让EF Core生成带筛选条件的数据库外键（仅支持SQL Server等兼容的数据库），彻底避免跨表的主键检查冲突。
3. 插入ProductSerial数据时，必须正确设置InvoiceType字段，否则导航属性无法正确关联到对应的发票实体。

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