Home » Ado.Net » Entity Framework » Entity Framework 4.3 delete cascade with code first (Poco)

Entity Framework 4.3 delete cascade with code first (Poco)

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.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

6 Responses so far.

  1. david says:

    Thanks for the article, Patrick. So does this mean that you must create a bi-directional relationship between parent and child objects? Ie Parent must contain reference to Child (or list of Childs) and Child must contain reference to Parent?

    • Hi David,
      With Entity Framework, you are not required to have bi-directional relationship to make Delete Cascade work as long as you have the parent class having a list of children.

      You can see this example from StackOverflow that show you how to create a Delete Cascade without having cycle reference.

  2. […] Entity Framework 4.3 delete cascade with code first (Poco) […]

  3. […] Entity Framework 4.3 delete cascade with code first (Poco) […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.