EF Core - code first - multiple cascade paths

Question

I know a lot has been written about that subject, so let me say first I carefully read first 2 pages on Google about this topic.

Many suggest to put DeleteBehavior.Restrict, and dotnet ef database update does not complain anymore. Problem solved until you try to delete record.

I have exactly the same problem as in this SO question, which is duplicate to this one. Proposed solution in second link is:

You are expected to break the cycle. You can do that by turning off cascade delete (by including WillCascadeOnDelete(false) in the respective relationship configuration) for at least one of the relationships customers->payments or customers->billingCenters.

This is what I research so far.
Now let's get to the problem (again). I have diamond relationship:

All ForeignKeys must be not null. So setting one foreign key to allow null is not an option. I would also like that user can delete:

• Parameter-Value-Parts -> No problem here
• Parameter-Values -> No problem here OnDelete(DeleteBehavior.Cascade)
• Parameter-Parts -> No problem here OnDelete(DeleteBehavior.Cascade)
• Parameters -> Big problem

To follow answer on this SO question, I could break cycle between Parameter-Parts and Parameter-Value-Parts with OnDelete(DeleteBehavior.Restrict).
This would allow me to delete Parameter, but now I can not delete Parameter-Parts.

I could manually delete all Parameter-Value-Parts before deleting Parameter-Parts, but I would like to avoid manually deletion. Is that possible?

I also read on SO, that all cascade delete should be avoided in application and developer should manually take care of deleting dependent table rows before row deletion. What is consider best practice?
Delete cascade seems easy solution, but I am not searching for an easy solution but the right one. The one that would scale easily on large data model in large application.

Answer 1

I hope I understand your requirements correctly.

If you want a real diamond shape, then it would be required for ParameterValueParts to reference ParameterValues and ParameterParts that in turn reference the same Parameter. This would be modelled by composite keys in SQL:

class Parameter
{
    public int ParameterId { get; set; }
}

class ParameterValue
{
    // composite key of ParameterId, ValueId with ParameterId also being a foreign key
    public int ParameterId { get; set; }
    public int ValueId { get; set; }
}

class ParameterPart
{
    // composite key of ParameterId, PartId with ParameterId also being a foreign key
    public int ParameterId { get; set; }
    public int PartId { get; set; }
}

class ParameterValueParts
{
    // key
    public int Id { get; set; }

    // three foreign keys:
    // ParameterId as foreign key to Parameter
    // ParameterId, ValueId are the composite foreign key to ParameterValue
    // ParameterId, PartId are the composite foreign key to ParameterPart
    public int ParameterId { get; set; }
    public int ValueId { get; set; }
    public int PartId { get; set; }
}

This way, you can have many combinations of ParameterValue and ParameterPart but each combination is required to belong to a specific Parameter. With this basic design, I never had any cascade issues in my projects.

Side note: you can configure the ValueId and PartId as DatabaseGeneratedOption.Identity in their respective classes in order to not handle ID values manually. (at least in EF6, I hope EF Core works similar in this regard)