Entity Framework Code first: cycles or multiple cascade paths

Question

I have a Booking class that has a booking contact (a Person) and a set of navigation properties (People) that links through a join table to another set of navigation properties (Bookings) in Person. How do I generate the Booking table with cascading deletes enabled for the booking contact relationship? When I leave it out of the fluent API code (default setting of cascade delete enabled) I get the following error message from migration:

Introducing FOREIGN KEY constraint 'FK_dbo.BookingPeople_dbo.People_PersonID' on table 'BookingPeople' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint or index. See previous errors.

 modelBuilder.Entity<Person>()
   .HasMany<Booking>(s => s.aBookings)
   .WithRequired(s => s.Contact)
   .HasForeignKey(s => s.ContactId); 


 modelBuilder.Entity<Booking>()
   .HasMany(t => t.People)
   .WithMany(t => t.Bookings)
   .Map(m => {
     m.ToTable("BookingPeople");
     m.MapLeftKey("BookingID");
     m.MapRightKey("PersonID");
   });

ocuenca · Accepted Answer · 2015-03-15T23:24:22.413

The problem is you have multiple paths of cascade deletes that could end trying to delete the same row in the BookingPeople table in DB.

You can avoid such ambiguous delete paths by either disabling cascading delete in the one-to-many relationship using Fluent API:

    modelBuilder.Entity<Booking>()
                .HasRequired(s => s.Contact)
                .WithMany(s => s.aBookings)
                .HasForeignKey(s => s.ContactId)
                .WillCascadeOnDelete(false);

Or by defining the relationship as optional (with a nullable foreign key, but you can not configure the relationship with cascade delete using Fluent Api).

     modelBuilder.Entity<Booking>()
            .HasOptional(s => s.Contact)
            .WithMany(s => s.aBookings)
            .HasForeignKey(s => s.ContactId);// ContactId is a nullable FK property

Also, you can remove the cascade delete convention by using:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

Or in the case of the many-to-many relationship:

modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

If you need to delete all the Bookings asociated with a Person when you delete it, my advice is configure the one-to-many relationship as optional, and override the SaveChanges method:

public override int SaveChanges()
{
    Bookings.Local
            .Where(r => r.ContactId == null)
            .ToList()
            .ForEach(r => Bookings.Remove(r));

    return base.SaveChanges();
 }

If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null. This way, you can find the orphans in the SaveChanges method and delete them

I don't know if I understand my suggestion 100% because I'm new to this, but is it possible to create a unidirectional one-to-many relationship, so that there's no need for aBookings navigational properties and would that fix it somehow or would the delete paths still be confused? — James Peters, Mar 16 '15 at 13:19
Even if you have an unidirectional one-to-many relationship that won't resolve the problem, You'll still have multiples paths could end trying to delete the same row. For that reason is my advise of defining the one-to-many relationship as optional, and if you need to delete te orphans then override the SaveChanges method. I think this link would help you understand better this solution:[Deleting orphans with Entity Framework](http://blog.oneunicorn.com/2012/06/02/deleting-orphans-with-entity-framework/) — ocuenca, Mar 16 '15 at 13:59
Thank you, @octavioccl. That's a very comprehensive explanation, and effectively covers all of the options I've been able to identify from other threads. Many threads on the topic address how to *disable* cascading deletes, but not how to *work around* the problem if you, indeed, require the behavior. I was hoping to avoid writing manual delete methods for each relationship, but it also makes sense why this limitations exists, particularly when thinking about it from SQL Server's perspective. — Jeremy Caney, Apr 30 '15 at 22:18
this answer doesn't make sense in case of M2M relationships. If you delete a Booking and cascade delete related BookingPeople in the join table, it shouldn't prevent you from doing that even when there are paths from People into the same join table. — Riz, Jun 06 '18 at 18:16

Entity Framework Code first: cycles or multiple cascade paths

1 Answers1

Linked

Related