Tip 33 – How cascade delete really works in EF
Imagine that in your database you have a cascade delete on an FK relationship.
Something like this:
Here the Delete Rule says that when a Category is deleted all the related Products should be deleted too.
If you generate an EF model from this database you get a model that on the surface looks no different from normal:
But if you dig into the CSDL section of the XML you will see this:
<Association Name="FK_Products_Categories">
<End Role="Categories" Type="TipsModel.Store.Categories" Multiplicity="1">
<OnDelete Action="Cascade" />
</End>
<End Role="Products" Type="TipsModel.Store.Products" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="Categories">
<PropertyRef Name="ID" />
</Principal>
<Dependent Role="Products">
<PropertyRef Name="CategoryID" />
</Dependent>
</ReferentialConstraint>
</Association>
Notice the <OnDelete> element, this tells the EF that when a Category is deleted the related Products *will* be too.
I deliberately said *will* and rather than *should*, because the EF does not take responsibility for cascading the delete in the database.
The EF is responsible for the correctness of the ObjectContext after SaveChanges(). So the EF attempts to synchronize the ObjectContext, with the expected database state after the expected cascade in the database.
A tell tale sign of this is that if you open up something like SqlProfiler, you will notice the EF issuing DELETE requests for dependent entities that it knows about (i.e. that are loaded in the ObjectContext) when a principal is deleted.
Essentially what is happening here is that the Entity Framework expects that deleting the principal in the database, will delete all it’s dependents in the database. So it issues, what should be, a redundant DELETE to request itself so the dependents already loaded are deleted from the ObjectContext.
The key thing to note is that the EF *does not* retrieve all the dependent entities and issue deletes for them: It only deletes dependents that are already in memory.
So here are the golden rules:
- If you add an Cascade delete rule to the model, you MUST have a corresponding DELETE rule in the database.
- If you absolutely insist on breaking rule (1) for some reason, Cascade will only work if you have all the dependents loaded in memory.
- (2) is *not* recommended!!!
While we do our best to keep the ObjectContext and database in sync, our attempts can fail if you have multiple levels of cascade delete.
For example, if you have this:
Category –> Product –> Order
And deleting a Category deletes its Products which in turn deletes its Orders.
The EF can, in rare circumstances, fail to sync up with the database when you delete a Category.
For example if you have an Order loaded that is related to a Category via an unloaded Product, and you delete the Category,the EF won’t know to delete the Order.
This means the Order will remain in the ObjectContext in the unchanged state, despite it having been deleted in the database.
Forewarned is forearmed.
Comments
- Anonymous
November 18, 2009
Hi,I have a question. When I tried adding the OnDelete action I still get the following error.{"The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers". The conflict occurred in database "C:\METADATAPROVIDERSAMPLE\METADATAPROVIDERWEBSITE\APP_DATA\NORTHWND.MDF", table "dbo.Orders", column 'CustomerID'.rnThe statement has been terminated."}Here is my code:NORTHWNDEntities1 ne = new NORTHWNDEntities1(); var originalEntity = (from entity in ne.Customers where entity.CustomerID == "ANTON" select entity).FirstOrDefault(); ne.DeleteObject(originalEntity); ne.SaveChanges(true);Here is the XML file contents: <Association Name="FK_Orders_Customers"> <End Role="Customers" Type="NORTHWNDModel.Store.Customers" Multiplicity="0..1" > <OnDelete Action="Cascade" /> </End> <End Role="Orders" Type="NORTHWNDModel.Store.Orders" Multiplicity="*" > </End> <ReferentialConstraint> <Principal Role="Customers"> <PropertyRef Name="CustomerID" /> </Principal> <Dependent Role="Orders"> <PropertyRef Name="CustomerID" /> </Dependent> </ReferentialConstraint> </Association>Oh, and I did make the changes in the database.Any help would be greatly appreciated!!!ThanksCodeWarrior - Anonymous
November 27, 2009
So just for clarification, since #2 is not recommended, are you saying that the #1 recommended approach is having a cascading delete set up on the physical database? Thanks. - Anonymous
November 27, 2009
@Luke,Absolutely, you should have the a cascade delete rule in the database, because EF can't be relied upon to delete every entity. It will only delete entities it knows about (i.e. that are in memory).Alex - Anonymous
December 12, 2009
Hi,I am used to a different ORM based on ActiveRecord where it is possible to ensure that additional cleanup is performed on delete if necessary.This obviously comes at a cost as you need to retrieve the related entity in order to carry out its ondelete cleanup.Lets say we have a Blog with Post that can have Attachments, and the actual files are stored on a file server with only a path in the database.Now I want to ensure that deleting a Blog or a Post will always cleanup the files in the file system too.How would you go about implementing that in EF?Thanks,Thomas - Anonymous
December 12, 2009
Thomas,Well you could do this by overriding SaveChanges, enumerating all the objectStateEntries in the ObjectContext.ObjectStateManager, looking for those in the deleted state.When you find one of the right type, you could have a rule that loads all the dependents, then when you finally call through to base.SaveChanges() EF would delete all the dependents even if there isn't a cascade delete rule in the database.Alex - Anonymous
August 13, 2012
Hi Alex,I couldn't get you , how exactly you override SaveChanges and delete all the Dependents.Can you explain with little more detail.It would be helpful if provided a sample code or with an example.