Entity Framework 4.3 delete cascade with code first (Poco)

Posted on: 2012-04-19

If you have a parent->child relationship between two of your classes and you delete the parent, you may want to delete all children. To do, you need to have a DELETE CASCADE statement on your foreign key. Here is a simple example:

CREATE TABLE [Parent]( [ID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](100) NULL, CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED GO

CREATE TABLE [ParentDetail]( [ID] [int] IDENTITY(1,1) NOT NULL, [Parent_ID] [int] NOT NULL, CONSTRAINT [PK_ParentDetail] PRIMARY KEY CLUSTERED GO

ALTER TABLE [ParentDetail] WITH CHECK ADD CONSTRAINT [FK_ParentDetail_Parent] FOREIGN KEY([Parent_ID]) REFERENCES [Parent] ([ID]) ON DELETE CASCADE GO 

The problem is that if you are using Entity Framework 4.3 and try to delete a Parent entity, you will end having this error :

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

The inner exception message will contain something similar to this:

Cannot insert the value NULL into column 'Parent_ID', table 'ParentDetail'; column does not allow nulls. UPDATE fails. The statement has been terminated.

So, what does it means? It tries to delete the Parent and to set into each ParentDetail the ID NULL because it's been erased. This is not what we want. In fact, we would like to have all ParentDetail to me removed as well. This is a little bit the reason why we have specify on the SQL to have a cascade.

You can do it manually in your project:

var listDetail = parent.ParentDetails.ToList(); 
foreach (var ParentDetail in listDetail){
   Database.ParentDetails.Remove(ParentDetail);
}

Database.Parents.Remove(Parent); Database.SaveChanges(); 

This will produce multiple amount of SQL statement on the SQL Server. One for each details and 1 for the Parent itself.

But, if you go to your database context and you specify in the OnModelCreating a rule about the cascade it will work as it's suppose to do.

protected override void OnModelCreating(DbModelBuilder modelBuilder) { 
  //... 
  modelBuilder.Entity<Parent>().HasMany(e => e.ParentDetails).WithOptional(s => s.Parent).WillCascadeOnDelete(true); 
  //... 

That's it! Now you can simply delete the Parent without having to delete manually every children.

Database.Parents.Remove(Parent); 
Database.SaveChanges(); 

On the SQL server side, you can see the database to have the same amount of delete statement executed. So, you do not save on the amount of query but save on the amount of logic to handle on the C# side because you do not have to care to delete every details.

On a special note, you do not need to have the table having a reference with the On Delete Cascade. You can handle the cascade only on the Entity DbContext with OnModelCreating. If you specify it on the Sql Server Database side, this only will enforce the integrity on the database side but won't be automatically applied on the delete with EF.