Entity Framework Core, DELETE CASCADE, and [Required]

Question

I am running into an issue DELETE CASCADE in Entity Framework Core that I can't seem to find a good solution to.

Here's a super simplified version of my model:

User {UserID, Name}
Recipe {RecipeID, UserID}
Ingredient {IngredientID, UserID}
RecipeIngredient {RecipeID, IngredientID} *RecipeIngredient is the many-to-many table.

Recipe and Ingredient both have UserID marked as [Required], and RecipeIngredient has RecipeID and IngredientID marked as [Required].

The issue is that SQL will not create the database because there are multiple cascade delete paths to RecipeIngredient ("Introducing FOREIGN KEY constraint... may cause cycles or multiple cascade paths").

So I'm stuck... I've worked through a few ideas, but nothing has quite worked.

1. Is there a design solution here? I'd like to keep my Foreign Keys since it makes sense to enforce it, but if there is a design solution, I'm open to it.

2. My next idea was to remove all FKs pointing back to User - I'd have to enforce the referential integrity during DELETE via my C# code and I could enforce the entry of the data during CREATE using [Required]. The problem with that - [Required] creates a FK, and adds "ON DELETE CASCADE," which puts us right back into the multiple cascade delete path problem. I'd really like to keep [Required] because of the slick integration with Razor pages, client side validation and errors, etc.

3. Next idea, set the cascade behavior to SetNull in OnModelCreating(...):

   modelBuilder.Entity(). HasOne(i => i.User) .WithMany(u => u.Ingredients) .OnDelete(DeleteBehavior.SetNull);

   modelBuilder.Entity() .HasOne(r => r.Source) .WithMany(s => s.Recipes) .OnDelete(DeleteBehavior.SetNull);

But this throws an exception, because even though my property in Ingredient and Recipe is set to Nullable:

[Required(ErrorMessage = "User ID is required.")] 
public Nullable<int> UserID { get; set; }

... EF still creates it as a NOT NULL database column due to the [Required] attribute.

What's the solution for this? As far as I can tell, I should just remove all FKs to User, and try to enforce it as a required field on CREATE, but I don't see a way to do that with data annotations, which I'd like to do to keep this logic in my code first model.

Answer 1

I'd recommend to disable cascade deletes as normally developers want to be very careful with what data is deleted and disabling will give you more fine grain control over your data in regards to deletion.

You can do so in a OnModelCreation(DbModelBuilder modelBuilder) override as follows in your Context.cs class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
  modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
}

In EF Core, the Conventions class is not available, so you'll need to iterate through your entity types and restrict deletion to achieve the desired affect:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  foreach (var relationship in builder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
  {
        relationship.DeleteBehavior = DeleteBehavior.Restrict;
  }
}