Am I Understanding This Correctly? FK constraint may cause cycles or multiple cascade paths'

Question

I'm struggling to get my head around the sql exception:

FK contstraint may cause cycles or multiple cascade paths

I know there are many posts relating to it here on SO, for example: https://stackoverflow.com/a/852047/1778169 and https://stackoverflow.com/a/17127512/1778169

I have read them a few times, but I still don't understand:

1. exactly what this error means, and
2. how I can design models to avoid it

My attempt at understanding - have I got this right?

In the models below, User is required for both ForumThread and ForumPost entities.

Deleting a User will cascade like this: User > ForumThread > ForumPost and also like this: User > ForumPost, thus giving me 'multiple cascade delete paths'.

public class ForumThread
{
  public int ID {get;set;}
  public int UserID {get;set;}
  public User User {get;set;}
  public Collection<ForumPost> Posts {get;set;}
}

public class ForumPost
{
  public int ID {get;set;}
  public int UserID {get;set;}
  public User User {get;set;}
  public int ForumThreadID {get;set;}
  public int ForumThread Thread {get;set;}
}

public class User
{
  public int ID {get;set;}
  public int UserID {get;set;}
  public User User {get;set;}
  public Collection<ForumThread> Threads {get;set;}
}

If I have understood this correctly, then how would I design the models so that a User is required for both ForumThread and ForumPost entities?

I'd like the User property on the ForumThread to identify and list threads started by a particular user.

I guess one option would be to select threads by identifying the user who made the first post. But isn't that overly complicated for something that should be quite simple?

Answer 1

Your model is fine (except I don't get why User has a user property and a single Thread instead of a collection of Threads, but I assume it's just a copy-and-paste error). If the business meaning of all relationships is that they are required I would model them as required.

The exception you are facing is not a problem of the model itself but of mapping this model to a particular relational database (SQL Server?). The exception is thrown by the database engine and not by Entity Framework. EF doesn't care about multiple cascading delete paths and other database engines might support them. But the one you are using apparently doesn't.

So, it's a technical limitation of the database system and the best way to solve the problem is disabling cascading delete for some or all relationships with Fluent API (see the second link in your question). I would not adjust the model (like defining some relationships as optional with nullable userID? FKs) to make it "compatible" with the particular database. After all a conceptual model should be - as much as possible - a database ignorant idea.

Of course, disabling cascading delete changes a bit the way how you would delete a user with all threads but there is no way to avoid it. You can't just rely anymore on the database deleting all threads (and posts) automatically when you delete a user. You must delete the user and all his threads manually now by calling DbSet<User>.Remove and DbSet<ForumThread>.Remove in a loop.

I don't know exactly your business requirements, but it seems a bit unusual to me that when a user gets deleted really all of his threads and all posts of those threads (including the posts of other users) get deleted as well. Wouldn't it be more appropriate to assign the threads and posts to some system user like Anonymous or Community? It that case you wouldn't even want to have cascading delete on the User relationships.