Cascading deletes with Entity Framework - Related entities deleted by EF

Question

I have an issue with deletion in Entity Framework. In short, EF explicitly tries to delete an entity from the database even though I've explcitly configured EF to use cascading deletes in the database.

My design:

I have three entity types, MainEntity, EntityTypeA and EntityTypeB. EF has been configured to use cascade deletion when deleting EntityTypeA and EntityTypeB. In other words, if I delete an instance of MainEntity, I want all related EntityTypeA and EntityTypeB instances to be deleted as well. I never delete EntityTypeA or EntityTypeB without also deleting their parent.

My problem is that EF explictly issues a DELETE statement for EntityTypeA, which causes my application to crash.

This is what my model look like:

The relations have the following non-default config:

• MainEntity -> EntityTypeA OnDelete: Cascade
• MainEntity -> EntityTypeB OnDelete: Cascade

The relation EntityTypeA -> EntityTypeB has OnDelete: None

Database contents

INSERT INTO MainEntities (Name) values ('Test') insert into EntityTypeA (MainEntityID) VALUES (1) insert into EntityTypeB (MainEntityID, EntityTypeAId) VALUES (1, 1) insert into EntityTypeB (MainEntityID, EntityTypeAId) VALUES (1, 1) 

My code:

class Program {    static void Main(string[] args)    {       var context = new Model1Container();       var mainEntity = context.MainEntities.Include("EntityTypeA").SingleOrDefault();       context.DeleteObject(mainEntity);       context.SaveChanges();    } } 

What happens

When I call SaveChanges, Entity Framework executes the following in the database:

exec sp_executesql N'delete [dbo].[EntityTypeA] where ([Id] = @0)',N'@0 int',@0=1 

This causes an foreign key violation, because there are items in EntityTypeB's table referencing EntityTypeA instances.

Question

Why does Entity Framework issue an explicit delete for the instance of EntityTypeA even though I've configured Entity Framework to use cascading deletes? If I remove the Include("EntityTypeA") it starts working again.

Answer 1

This is exactly how cascading deletes behaves in EF. Setting Cascade on a relation in EF designer instructs EF to execute DELETE statement for each loaded realated entity. It doesn't say anything about ON CASCADE DELETE in the database.

Setting Cascade deletion when using EF needs two steps:

• Set Cascade on relation in EF designer. This instruct context that all loaded related entities must be deleted prior to deletion of the parent entity. If this doesn't happen EF will throw exception because internal state will detect that loaded childs are not related to any existing parent entity even the relation is required. I'm not sure if this happens before execution of delete statement of the parent entity or after but there is no difference. EF doesn't reload related entities after executing modifications so it simply doesn't know about cascade deletes triggered in the database.
• Set ON CASCADE DELETE on relation in database. This will instruct SQL to delete all related records which were not loaded to context in the time of deleting the parent.

The implementation of cascade deletes in EF is strange and quite inefficient but this is how it behaves and if you want to use it, you must modify your application to behaves correctly in this scenario.

Answer 2

Instead of on the EF designer, one can also set cascade deletion on the FK Constraints in the database.

Here are the visual steps from Sql Server Management Studio (SSMS) on how to setup cascading deletes.

Note when done, don't forget to update the edmx against the database before attempting a delete.

I discuss this in more depth on my blog: Entity Framework Cascading Deletes; Set it from the database.