How to cascade deletion only in the connection table using Entity Framework First Fluent API code

Question

I am trying to map an existing database using the Entity Framework First Fluent API. I am struggling with many relationships and the delete cascade.

Here are my two POCO classes:

public class Foo { public int FooId; public string FooDescription; public IList<Bar> Bars; } public class Bar { public int BarId; public string BarDescription; } 

I currently have a navigation property from Foo to Bar . This is because in my real-world scenario it makes no sense to switch from Bar to Foo . The ratio from Foo to Bar is actually 0, n, which means that one instance of Foo does not require instances of Bar .

My existing database has a join table between the two tables. This table looks like this:

 create table Foo_Bar( FooId int not null, BarId int not null ); 

Since I prefer not to create such a POCO class in my project, I mapped the table as follows:

 modelBuilder.Entity<Foo>() .HasMany(t => t.Bars) .WithMany() .Map(m => { m.ToTable("Foo_Bar"); m.MapLeftKey("FooId"); m.MapRightKey("BarId"); }); 

This works great for inserts. Insert an object into Foo and then into an existing Foo_Bar table.

But when I delete Foo , I would like to delete entries in the connection table, but not in the child table. Value I want to delete entries on Foo_Bar , but not extend this cascade to Bar .

Is such a display possible? I should mention that in my context I deleted both OneToManyCascadeDeleteConvention and ManyToManyCascadeDeleteConvention , because in 99% of cases I will not use cascading deletion. This is one specific situation.

Tracing the SQL generated by the Entity Framework, I also noticed that if I extract the Foo instance and set all its bars inside EntityState.Deleted, EF will delete both records in Foo_Bar and Bar . The last thing I should mention is that I am working in a disconnected script.

I tried to search, but it seems that I am not using the correct keywords, because I could not find anyone in this situation. At this point, the only solution I can see is the Map a FooBar , but I hope there is a way to do this.

- Change

It seems like my removal approach was wrong at first. When I tried to delete, I did not pass an instance of Foo with the Bar list populated. After this change, Entity Framwork creates a delete statement for each Bar in the list. I still have problems updating, such as adding and removing items at the same time. Will do more tests and post here.

Thanks in advance.

Answer 1

But when I delete Foo, I would like to delete the entries on join, but not in the child table. Value I want to delete entries on Foo_Bar, but not to extend this cascade to Bar.

Is such a display possible?

This is actually the only mapping possible. You cannot extend cascading deletion to the Bar table, because in relation (from the point of view of the database schema), the Foo_Bar join Foo_Bar is relationship-dependent, and Bar is the main one. As a rule, cacscading delete is effective only when the principal is deleted, but not when the dependent is deleted.

So, you don’t need to worry that Bar can be deleted when you delete Foo , and you don’t need to apply any special settings for this.

But you have a problem when you deleted ManyToManyCascadeDeleteConvention . It acts globally for the entire model, and there is no way to re-enable cascading deletion for a specific many-to-many relationship. (For many-to-many relationships, there is no WillCascadeOnDelete(true) , for example, for one-to-many or one-to-one relationships.)

Are you sure that removing ManyToManyCascadeDeleteConvention really required in your model? I have never come across a model where it would be advisable not to delete related entries in the join table if one of the joined objects ( Foo or Bar ) is deleted.