Entity Framework Code First 5 Cascade Delete many tables error

Question

I have this model

public class State { public State() { this.Promotions = new List<Promotion>(); this.Branches = new List<Branch>(); this.Stores = new List<Store>(); } public int Id { get; set; } public string Description { get; set; } public virtual ICollection<Promotion> Promotions { get; set; } public virtual ICollection<Store> Stores { get; set; } public virtual ICollection<Branch> Branches { get; set; } } public class Store { public Store() { this.Promotions = new List<Promotion>(); this.Branches = new List<Branch>(); } public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Promotion> Promotions { get; set; } public virtual ICollection<Branch> Branches { get; set; } public int StateId { get; set; } // Foreign key public virtual State State { get; set; } // Navigation Property } public class Branch { public Branch() { this.Promotions = new List<Promotion>(); } public int Id { get; set; } public string Name { get; set; } public int StoreId { get; set; } // Foreign key public int StateId { get; set; } // Foreign key public virtual Store Store { get; set; } // Navigation Property public virtual State State { get; set; } // Navigation Property public virtual ICollection<Promotion> Promotions { get; set; } } public class Promotion { public Promotion() { this.Stores = new List<Store>(); this.Branches = new List<Branch>(); this.Productos = new List<Producto>(); } public int Id { get; set; } public string Name { get; set; } public int StateId { get; set; } public virtual ICollection<Store> Stores { get; set; } public virtual ICollection<Branch> Branches { get; set; } public virtual ICollection<Product> Products { get; set; } public virtual State State { get; set; } } 

And this is in my context:

 // State modelBuilder.Entity<State>() .HasMany(p => p.Promotions) .WithRequired(e => e.State) .WillCascadeOnDelete(false); modelBuilder.Entity<State>() .HasMany(s => s.Branches) .WithRequired(e => e.State) .WillCascadeOnDelete(false); modelBuilder.Entity<State>() .HasMany(e => e.Stores) .WithRequired(e => e.State) .WillCascadeOnDelete(true); // Store modelBuilder.Entity<Store>() .HasMany(b => b.Branches) .WithRequired(s => s.Store) .WillCascadeOnDelete(true); // Many to many modelBuilder.Entity<Store>(). HasMany(p => p.Promotions). WithMany(s => s.Stores). Map( m => { m.MapLeftKey("StoreId"); m.MapRightKey("PromotionId"); m.ToTable("Store_Promotion"); }); modelBuilder.Entity<Promotion>(). HasMany(e => e.Products). WithMany(p => p.Promotiones). Map( m => { m.MapLeftKey("PromotionId"); m.MapRightKey("ProductoId"); m.ToTable("Promotion_Producto"); }); modelBuilder.Entity<Branch>(). HasMany(p => p.Promotiones). WithMany(b => b.Branches). Map( m => { m.MapLeftKey("BranchId"); m.MapRightKey("PromotionId"); m.ToTable("Branch_Promotion"); }); 

Now, if I turn on more than one WillCascadeOnDelete state (the first three are in free display), I get an error

The testing method Proj.Data.Tests.UnitTest1.TestPromotion threw an exception:
System.Data.SqlClient.SqlException: introducing the FOREIGN KEY constraint "FK_dbo.Branch_dbo.Store_StoreId" in the Branch table can cause loops or multiple cascading paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION or change other FOREIGN KEY constraints. Failed to create constraint. See Previous Errors.

I know this, and I read Julia Lerman's book:

Some databases (including SQL Server) do not support multiple relationships that indicate cascading deletes that point to the same table

How this happens because the many to many relationship table has cascading deletion coming from both related tables.

So my question is: the only choice here is to disable cascading deletion on the parent tables and handle the deletion in the relationship table manually? Isn't there a workaround from Entity Framework 5 for this?

Answer 1

Ok, I understood the problem. It doesn’t have to be many, many relationships, the problem is this

 State -> Promotion -> PromotionStore State -> Branch -> BranchPromotion State -> Store -> StorePromotion 

and then Store, Branch, and Store all have FK status. Therefore, if I remove the State PromotionStore, you will be able to achieve the 1st and 3rd possibilities.

As a result, I disabled cascading deletion for the state and manually deleted the related records:

 public override void Delete(State state) { DbContext.Entry(state).Collection(x => x.Promotions).Load(); DbContext.Entry(state).Collection(x => x.Stores).Load(); DbContext.Entry(state).Collection(x => x.Branches).Load(); var associatedPromotions = state.Promotions.Where(p => p.StateId == state.Id); associatedPromotions.ToList().ForEach(r => DbContext.Set<Promotion>().Remove(r)); var associatedStores = state.Stores.Where(e => e.StateId == state.Id); associatedStores.ToList().ForEach(e => DbContext.Set<Store>().Remove(e)); var associatedBranches = state.Branches.Where(s => s.StateId == state.Id); associatedBranches.ToList().ForEach(s => DbContext.Set<Branch>().Remove(s)); base.Delete(state); }