How to avoid cycles when introducing FOREIGN KEY constraint in a table?-CodePudding

I'm using code-first approach with EF Core 6 to construct the database and its tables. The problem is that when I update the database, I get an error regarding cycles or multiple cascade paths. I searched the same questions but none of them could solve my problem. My entity classes are:

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; }
}

My context class:

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

    }

I get the following error when update the database:

Introducing FOREIGN KEY constraint 'FK_PmDataSets_Equipments_EquipmentId' on table 'PmDataSets' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

CodePudding user response：

you need to add some extra config like this :

    protected override void OnModelCreating(ModelBuilder mb)
{   
    var pmConfig = mb.Entity<PmDataSet>();
    pmConfig.HasOne(n => n.Equipment).WithMany(n => n.PmDataSets).OnDelete(DeleteBehavior.NoAction);
}

the problem is your Equipment model or/and some others already has at least one cascade delete relation, so if you add another cascade delete relation there will be a chain deletion if one of these entities got deleted. so you need to tell ef that some/none of these relations are not cascading while deleting.

(A) <-- cascade --> (B)

(B) <-- cascade --> (C) so there will be a logical cascade between A and C, that is not arbitrary.

with a little playing with that config you will solve your problem ;)

-------- update to answer the commented question u can go by ef convictions:

public User MainUploader { get; set; }
[Required]
public int MainUploaderId { get; set; }
public User Uploader { get; set; }
public int? UploaderId { get; set; }

you also can use [ForeignKey("fkName")] annotation if you want to use another way of naming your properties, or use fluent API ForeignKey(a=> a.something) method or its overlods.