Introducing FOREIGN KEY constraint xx on table yy may cause cycles or multiple cascade paths

Question

I have read this answer, however, it is over 2 years old and I was hoping that there may be an answer dealing specifically with Entity Framework.

To explain quickly, I have a type that can refer to itself, I have been over and over the schema, and I am certain that this is the best way to achieve it...

It will never have itself as a reference, it would always be a new one, so, this error should not be an issue.

That being said, I completely understand that technically in SQL it can refer to itself, however, it never will (from the code).

Disabling cascade delete allows this to work, however, I really do not want to disable this as it is a complicated program and I take advantage of Cascade Delete "higher up" in the chain.

Answer 1

If SQL complains about multiple cascade paths there is not much to do you simply cannot use cascade delete in database for such case but you can use workaround - instead of using built-in cascade delete use INSTEAD OF DELETE trigger and write custom SQL to delete dependent record first and after that delete the principal record. In EF try to use cascade delete because the database layer should be transparent to EF in this case.