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:
<End Role="Categories" Type="TipsModel.Store.Categories" Multiplicity="1">
<OnDelete Action="Cascade" />
<End Role="Products" Type="TipsModel.Store.Products" Multiplicity="*" />
<PropertyRef Name="ID" />
<PropertyRef Name="CategoryID" />
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.