Entity Framework可选多父级实体的级联删除问题咨询

我们在政治竞选系统中设计了Campaign实体，它只能属于Country（全国竞选）或State（地方竞选）其中一个父级，对应实体模型如下：

public class Campaign : IOrganization
{
    public int Id { get; private set; }

    public State? State { get; set; }
    public int? StateId { get; set; }

    public Country? Country { get; set; }
    public int? CountryId { get; set; }
}

Country和State实体各自拥有对应的Campaign集合：

public class Country : IOrganization
{
    public int Id { get; private set; }

    public ICollection<Campaign>? Campaigns { get; private set; }
}

public class State : IOrganization
{
    public int Id { get; private set; }

    public ICollection<Campaign>? Campaigns { get; private set; }
}

执行update-database命令时，出现如下SQL错误：

Introducing FOREIGN KEY constraint 'FK_Campaigns_States_StateId' on table 'Campaigns' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

目前我们通过Fluent API设置OnDelete(DeleteBehavior.ClientSetNull)，并在DbContext.SaveChangesAsync()中手动处理删除来规避问题，但希望让数据库直接执行级联删除，需要告知EF不会出现一个Campaign同时拥有State和Country父级的情况，具体解决方案如下：

1. 添加数据库检查约束 #

通过Fluent API为Campaign实体添加数据库层面的检查约束，强制保证StateId和CountryId中只有一个不为null：

modelBuilder.Entity<Campaign>()
    .HasCheckConstraint("CK_Campaign_ParentConstraint", "StateId IS NOT NULL AND CountryId IS NULL OR StateId IS NULL AND CountryId IS NOT NULL");

这条约束会让数据库直接拦截同时设置两个父级ID的非法数据，从根源上消除多路径级联删除的可能性。

2. 配置数据库级联删除 #

添加检查约束后，就可以为两个外键正常配置级联删除规则：

// 配置State与Campaign的级联删除
modelBuilder.Entity<Campaign>()
    .HasOne(c => c.State)
    .WithMany(s => s.Campaigns)
    .HasForeignKey(c => c.StateId)
    .OnDelete(DeleteBehavior.Cascade);

// 配置Country与Campaign的级联删除
modelBuilder.Entity<Campaign>()
    .HasOne(c => c.Country)
    .WithMany(co => co.Campaigns)
    .HasForeignKey(c => c.CountryId)
    .OnDelete(DeleteBehavior.Cascade);

3. 实体层面添加校验逻辑 #

在Campaign实体内部添加属性校验，提前在应用层阻止非法赋值，配合数据库约束形成双重保障：

public class Campaign : IOrganization
{
    public int Id { get; private set; }

    private State? _state;
    public State? State 
    { 
        get => _state; 
        set 
        {
            if (value != null && Country != null)
                throw new InvalidOperationException("Campaign不能同时关联State和Country");
            _state = value;
            StateId = value?.Id;
        }
    }
    public int? StateId { get; private set; }

    private Country? _country;
    public Country? Country 
    { 
        get => _country; 
        set 
        {
            if (value != null && State != null)
                throw new InvalidOperationException("Campaign不能同时关联State和Country");
            _country = value;
            CountryId = value?.Id;
        }
    }
    public int? CountryId { get; private set; }
}

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