Entity Framework Code first: cycles or multiple cascade paths

c# ef-fluent-api entity-framework sql-server

Question

I have a class for booking with a booking contact (aPerson ) and a group of navigational attributes (People ) that connects to a different set of navigational attributes via a join table (Bookings ) inPerson How do I produce theBooking table for the booking contact connection with cascading deletes turned on? When I leave it out of the fluent API code (with cascade delete enabled as the default setting), migration gives me the following error message:

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");
   });
1
4
4/30/2015 11:15:55 PM

Accepted Answer

The issue is that there are many cascading delete paths that could result in attempting to delete the same row in the database.BookingPeople database table

By utilizing Flowing API to disable cascading delete in the one-to-many connection, you may avoid such unclear delete paths.

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

another approach is to make the relationship voluntary (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

Additionally, you can do it using the following method:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

Alternatively, in a many-to-many relationship:

modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

If you must remove all of theBookings connected to aPerson My recommendation is to make the one-to-many relationship optional when you delete it and override theSaveChanges method:

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

    return base.SaveChanges();
 }

Code First does not establish cascade delete on the relationship if a foreign key on the dependent entity is nullable, and when the principal is destroyed, the foreign key will be set tonull This will enable you to identify the orphans in theSaveChanges approach and remove them.

20
3/15/2015 11:24:22 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow