EF Core 6 Code First引入外键约束时如何避免循环级联报错

使用code-first开发模式基于EF Core 6构建数据库与对应数据表，执行update-database命令执行数据库更新时，触发外键循环或多重级联路径相关报错，检索同类问题的公开解决方案均未解决问题。

实体类定义 #

public class User
{
    [Key]
    public int UserId { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Username { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Password { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string UserRole { get; set; }
    [Required]
    public bool IsActive { get; set; }
    [Required]
    public bool CanAccessNotifications { get; set; }
    [Required]
    public bool CanAccessMessages { get; set; }
    [Required]
    public bool CanAccessFiles { get; set; }
    [Required]
    public bool CanAccessPmDatabase { get; set; }
    [Required]
    public bool CanMakeChangesToPmDatabase { get; set; }
    [Required]
    public bool IsLocked { get; set; }
    [Required]
    public bool CanLocked { get; set; }

    //Navigation properties
    public virtual CostCenter CostCenter { get; set; }
    public virtual List<PmSchedule> PmSchedules { get; set; }
}

public class ServiceType
{
    [Key]
    public int ServiceTypeId { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Title { get; set; }
    [Required]
    public bool IsActive { get; set; } = true;

    //Navigation properties
    public virtual List<PmDataSet> PmDataSets { get; set; }
    public virtual List<PmSchedule> PmSchedules { get; set; }
}

public class PmSchedule
{
    [Key]
    public long PmScheduleId { get; set; }
    [Required]
    public long PmNumber { get; set; }
    [Required]
    [DataType(DataType.DateTime)]
    public DateTime ScheduledStartDate { get; set; }
    [Required]
    [DataType(DataType.DateTime)]
    public DateTime ScheduledEndDate { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string MainFileName { get; set; }
    [DataType(DataType.Text)]
    public string? UploadedFileName { get; set; }
    [Required]
    public int MainUploader { get; set; }
    public int? Uploader { get; set; }
    [DataType(DataType.DateTime)]
    public DateTime? CompletionDate { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string? Status { get; set; }

    //Navigation properties
    public virtual CostCenter CostCenter { get; set; }
    public virtual ServiceType ServiceType { get; set; }
}

public class PmDataSet
{
    [Key]
    public long PmDataSetId { get; set; }
    [Required]
    public long PmNumber { get; set; }
    [Required]
    [DataType(DataType.DateTime)]
    public DateTime ScheduledStartDate { get; set; }
    [Required]
    [DataType(DataType.DateTime)]
    public DateTime ScheduledEndDate { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string WorkOrderNumber { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Priority { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Activity { get; set; }
    [DataType(DataType.DateTime)]
    public DateTime? StartTime { get; set; }
    [DataType(DataType.DateTime)]
    public DateTime? EndTime { get; set; }
    [Required]
    public int ActualDuration { get; set; }
    [Required]
    public int StandardDuration { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Executor { get; set; }
    [DataType(DataType.Text)]
    public string? Expaination { get; set; }


    //Navigation properties
    public virtual ServiceType ServiceType { get; set; }
    public virtual CostCenter CostCenter { get; set; }
    public virtual Equipment Equipment { get; set; }
}

public class Equipment
{
    [Key]
    public long EquipmentId { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string EquipmentCode { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string EquipmentTitle { get; set; }

    //Navigation properties
    public virtual CostCenter CostCenter { get; set; }
    public virtual List<PmDataSet> PmDataSets { get; set; }
}

public class CostCenter
{
    [Key]
    public int CostCenterId { get; set; }
    [Required]
    [DataType(DataType.Text)]
    public string Title { get; set; }
    [Required]
    public bool IsActive { get; set; } = true;


    //Navigation properties
    public virtual List<PmDataSet>? PmDataSets { get; set; }
    public virtual List<PmSchedule>? PmSchedules { get; set; }
    public virtual List<Equipment>? Equipments { get; set; }
    public virtual List<User>? Users { get; set; }
}

数据库上下文初始配置 #

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
}

报错信息 #

在表'PmDataSets'上引入FOREIGN KEY约束'FK_PmDataSets_Equipments_EquipmentId'可能导致循环或多重级联路径，请指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。无法创建约束或索引，请查看之前的错误详情。

SQL Server数据库本身不允许同一张子表存在多条来自上级表的级联删除路径。当前实体结构中，删除CostCenter表记录时，针对PmDataSet表存在两条级联删除路径：

• 路径1：CostCenter 直接关联PmDataSet，删除CostCenter时直接级联删除关联的PmDataSet记录
• 路径2：CostCenter 关联Equipment，删除CostCenter时先级联删除关联的Equipment记录，再通过Equipment和PmDataSet的关联级联删除对应PmDataSet记录
  EF Core 6默认对所有非空必填外键配置DeleteBehavior.Cascade级联删除行为，因此触发SQL Server的级联路径限制，抛出上述错误。

在OnModelCreating方法中手动配置产生多重路径的外键删除行为，切断冗余级联路径即可。

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    // 关闭Equipment指向PmDataSet的级联删除，切断第二条级联路径
    builder.Entity<PmDataSet>()
        .HasOne(p => p.Equipment)
        .WithMany(e => e.PmDataSets)
        .OnDelete(DeleteBehavior.NoAction);
}

操作注意事项 #

• 配置修改完成后，先执行Add-Migration FixCascadeError生成新的迁移文件，检查迁移文件中对应外键的onDelete配置值为NoAction后，再执行update-database即可完成更新
• 配置NoAction删除行为后，删除Equipment表记录前，需要先手动处理关联的PmDataSet记录（删除或修改关联外键值），否则会触发外键约束报错

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