How to cascade delete only on join table using Entity Framework Code First Fluent API

Question

I'm trying to map an existing database using Entity Framework Code First Fluent API. I'm struggling on a situation with a many-to-many relationship and cascade delete.

Here's 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;
}

For now, I only have a navigation property from Foo to Bar. That's because in my real scenario, it doesn't make sense for anyone to go from Bar to Foo. The relationship from Foo to Bar is actually 0,n which means one instance of Foo do not require instances of Bar.

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

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

Because I prefer to not create such a POCO class in my project, I mapped the table like this:

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

That works perfectly for inserts. Entity insert on Foo and then on the existing Foo_Bar table.

But when I delete a Foo, I would like to delete the records on the join table but not on the child table. Meaning I want to delete records on Foo_Bar but not to extend this cascade to Bar.

Is such a mapping possible? I should mention that on my context I removed both OneToManyCascadeDeleteConvention and ManyToManyCascadeDeleteConvention because 99% of the times I won't be using cascade delete. This is one specific situation.

By tracing the SQL generated by Entity Framework, I also noticed that if I retrieve a Foo instance and set all Bars inside it to EntityState.Deleted, EF will delete both records in Foo_Bar and Bar. One last thing I should mention is that I'm working in a disconnected scenario.

I tried to search this over but it seems I'm not using the right keywords as I could not find anyone in such a situation. At this point the only solution I see is to Map a FooBar class but I'd hope there's a way to do this.

--Edit

It appears that my approach to delete was wrong at first. When I tried deleting I wasn't passing the instance of Foo with its list of Bar filled. After that change, Entity Framwork creates a delete statement for every Bar on the list. I still have problems with updating like adding and removing items at the same time. Will do more tests and post here.

Thanks in advance.

Answer 1

But when I delete a Foo, I would like to delete the records on the join table but not on the child table. Meaning I want to delete records on Foo_Bar but not to extend this cascade to Bar.

Is such a mapping possible?

This is actually the only mapping that is possible. You cannot extend cascading delete to the Bar table because in the relationship (from database schema perspective) the Foo_Bar join table is the dependent in the relationship and Bar is the principal. Generally cacscading delete is only in effect if you delete the principal but not when you delete the dependent.

So, you don't need to worry that a Bar could be deleted when you delete a Foo and you don't have to apply any special setting for this.

But you have a problem when you have removed the ManyToManyCascadeDeleteConvention. It acts globally for the whole model and there is no option to turn on cascading delete again for a specific many-to-many relationship. (There is no WillCascadeOnDelete(true) for many-to-many relationship like there is one for one-to-many or one-to-one relationships.)

Are you sure that removing the ManyToManyCascadeDeleteConvention is really required in your model? I never encountered a model where it would make sense to not delete the related entries in the join table when one of the joined entities (Foo or Bar) is deleted.