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.
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:
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.