Entity Framework Core (EF Core) represents relationships using foreign keys. An entity with a foreign key is the child or dependent entity in the relationship. This entity's foreign key value must match the primary key value (or an alternate key value) of the related principal/parent entity.
If the principal/parent entity is deleted, then the foreign key values of the dependents/children will no longer match the primary or alternate key of any principal/parent. This is an invalid state, and will cause a referential constraint violation in most databases.
There are two options to avoid this referential constraint violation:
Set the FK values to null
Also delete the dependent/child entities
The first option is only valid for optional relationships where the foreign key property (and the database column to which it is mapped) must be nullable.
The second option is valid for any kind of relationship and is known as "cascade delete".
Tip
This document describes cascade deletes (and deleting orphans) from the perspective of updating the database. It makes heavy use of concepts introduced in Change Tracking in EF Core and Changing Foreign Keys and Navigations. Make sure to fully understand these concepts before tackling the material here.
Cascading deletes are needed when a dependent/child entity can no longer be associated with its current principal/parent. This can happen because the principal/parent is deleted, or it can happen when the principal/parent still exists but the dependent/child is no longer associated with it.
Deleting a principal/parent
Consider this simple model where Blog is the principal/parent in a relationship with Post, which is the dependent/child. Post.BlogId is a foreign key property, the value of which must match the Blog.Id primary key of the blog to which the post belongs.
public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Post> Posts { get; } = new List<Post>();
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
By convention, this relationship is configured as a required, since the Post.BlogId foreign key property is non-nullable. Required relationships are configured to use cascade deletes by default. See Relationships for more information on modeling relationships.
When deleting a blog, all posts are cascade deleted. For example:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).Include(e => e.Posts).First();
context.Remove(blog);
context.SaveChanges();
SaveChanges generates the following SQL, using SQL Server as an example:
-- Executed DbCommand (1ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Posts]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
-- Executed DbCommand (0ms) [Parameters=[@p0='2'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Posts]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
-- Executed DbCommand (2ms) [Parameters=[@p1='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Blogs]
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
Severing a relationship
Rather than deleting the blog, we could instead sever the relationship between each post and its blog. This can be done by setting the reference navigation Post.Blog to null for each post:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).Include(e => e.Posts).First();
foreach (var post in blog.Posts)
{
post.Blog = null;
}
context.SaveChanges();
The relationship can also be severed by removing each post from the Blog.Posts collection navigation:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).Include(e => e.Posts).First();
blog.Posts.Clear();
context.SaveChanges();
In either case the result is the same: the blog is not deleted, but the posts that are no longer associated with any blog are deleted:
-- Executed DbCommand (1ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Posts]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
-- Executed DbCommand (0ms) [Parameters=[@p0='2'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Posts]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
Deleting entities that are no longer associated with any principal/dependent is known as "deleting orphans".
Tip
Cascade delete and deleting orphans are closely related. Both result in deleting dependent/child entities when the relationship to their required principal/parent is severed. For cascade delete, this severing happens because the principal/parent is itself deleted. For orphans, the principal/parent entity still exists, but is no longer related to the dependent/child entities.
Entities in the database that have not been loaded into the context
Cascade delete of tracked entities
EF Core always applies configured cascading behaviors to tracked entities. This means that if the application loads all relevant dependent/child entities into the DbContext, as is shown in the examples above, then cascading behaviors will be correctly applied regardless of how the database is configured.
Many database systems also offer cascading behaviors that are triggered when an entity is deleted in the database. EF Core configures these behaviors based on the cascade delete behavior in the EF Core model when a database is created using EnsureCreated or EF Core migrations. For example, using the model above, the following table is created for posts when using SQL Server:
CREATE TABLE [Posts] (
[Id] int NOT NULL IDENTITY,
[Title] nvarchar(max) NULL,
[Content] nvarchar(max) NULL,
[BlogId] int NOT NULL,
CONSTRAINT [PK_Posts] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Posts_Blogs_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blogs] ([Id]) ON DELETE CASCADE
);
Notice that the foreign key constraint defining the relationship between blogs and posts is configured with ON DELETE CASCADE.
If we know that the database is configured like this, then we can delete a blog without first loading posts and the database will take care of deleting all the posts that were related to that blog. For example:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).First();
context.Remove(blog);
context.SaveChanges();
Notice that there is no Include for posts, so they are not loaded. SaveChanges in this case will delete just the blog, since that's the only entity being tracked:
-- Executed DbCommand (6ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Blogs]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
This would result in an exception if the foreign key constraint in the database is not configured for cascade deletes. However, in this case the posts are deleted by the database because it has been configured with ON DELETE CASCADE when it was created.
Note
Databases don't typically have any way to automatically delete orphans. This is because while EF Core represents relationships using navigations as well of foreign keys, databases have only foreign keys and no navigations. This means that it is usually not possible to sever a relationship without loading both sides into the DbContext.
Note
The EF Core in-memory database does not currently support cascade deletes in the database.
Warning
Do not configure cascade delete in the database when soft-deleting entities. This may cause entities to be accidentally really deleted instead of soft-deleted.
Database cascade limitations
Some databases, most notably SQL Server, have limitations on the cascade behaviors that form cycles. For example, consider the following model:
public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Post> Posts { get; } = new List<Post>();
public int OwnerId { get; set; }
public Person Owner { get; set; }
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
public int AuthorId { get; set; }
public Person Author { get; set; }
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Post> Posts { get; } = new List<Post>();
public Blog OwnedBlog { get; set; }
}
This model has three relationships, all required and therefore configured to cascade delete by convention:
Deleting a blog will cascade delete all the related posts
Deleting the author of posts will cause the authored posts to be cascade deleted
Deleting the owner of a blog will cause the blog to be cascade deleted
This is all reasonable (if a bit draconian in blog management policies!) but attempting to create a SQL Server database with these cascades configured results in the following exception:
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Posts_Person_AuthorId' on table 'Posts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
There are two ways to handle this situation:
Change one or more of the relationships to not cascade delete.
Configure the database without one or more of these cascade deletes, then ensure all dependent entities are loaded so that EF Core can perform the cascading behavior.
Taking the first approach with our example, we could make the post-blog relationship optional by giving it a nullable foreign key property:
public int? BlogId { get; set; }
An optional relationship allows the post to exist without a blog, which means cascade delete will no longer be configured by default. This means there is no longer a cycle in cascading actions, and the database can be created without error on SQL Server.
Taking the second approach instead, we can keep the blog-owner relationship required and configured for cascade delete, but make this configuration only apply to tracked entities, not the database:
Now what happens if we load both a person and the blog they own, then delete the person?
using var context = new BlogsContext();
var owner = context.People.Single(e => e.Name == "ajcvickers");
var blog = context.Blogs.Single(e => e.Owner == owner);
context.Remove(owner);
context.SaveChanges();
EF Core will cascade the delete of the owner so that the blog is also deleted:
-- Executed DbCommand (8ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Blogs]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
-- Executed DbCommand (2ms) [Parameters=[@p1='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [People]
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
However, if the blog is not loaded when the owner is deleted:
using var context = new BlogsContext();
var owner = context.People.Single(e => e.Name == "ajcvickers");
context.Remove(owner);
context.SaveChanges();
Then an exception will be thrown due to violation of the foreign key constraint in the database:
Microsoft.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Blogs_People_OwnerId". The conflict occurred in database "Scratch", table "dbo.Blogs", column 'OwnerId'.
The statement has been terminated.
Cascading nulls
Optional relationships have nullable foreign key properties mapped to nullable database columns. This means that the foreign key value can be set to null when the current principal/parent is deleted or is severed from the dependent/child.
Let's look again at the examples from When cascading behaviors happen, but this time with an optional relationship represented by a nullable Post.BlogId foreign key property:
public int? BlogId { get; set; }
This foreign key property will be set to null for each post when its related blog is deleted. For example, this code, which is the same as before:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).Include(e => e.Posts).First();
context.Remove(blog);
context.SaveChanges();
Will now result in the following database updates when SaveChanges is called:
-- Executed DbCommand (2ms) [Parameters=[@p1='1', @p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Posts] SET [BlogId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
-- Executed DbCommand (0ms) [Parameters=[@p1='2', @p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Posts] SET [BlogId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
-- Executed DbCommand (1ms) [Parameters=[@p2='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Blogs]
WHERE [Id] = @p2;
SELECT @@ROWCOUNT;
Likewise, if the relationship is severed using either of the examples from above:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).Include(e => e.Posts).First();
foreach (var post in blog.Posts)
{
post.Blog = null;
}
context.SaveChanges();
Or:
using var context = new BlogsContext();
var blog = context.Blogs.OrderBy(e => e.Name).Include(e => e.Posts).First();
blog.Posts.Clear();
context.SaveChanges();
Then the posts are updated with null foreign key values when SaveChanges is called:
-- Executed DbCommand (2ms) [Parameters=[@p1='1', @p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Posts] SET [BlogId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
-- Executed DbCommand (0ms) [Parameters=[@p1='2', @p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Posts] SET [BlogId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
The fixup of relationships like this has been the default behavior of Entity Framework since the first version in 2008. Prior to EF Core it didn't have a name and was not possible to change. It is now known as ClientSetNull as described in the next section.
Databases can also be configured to cascade nulls like this when a principal/parent in an optional relationship is deleted. However, this is much less common than using cascading deletes in the database. Using cascading deletes and cascading nulls in the database at the same time will almost always result in relationship cycles when using SQL Server. See the next section for more information on configuring cascading nulls.
Configuring cascading behaviors
Tip
Be sure to read sections above before coming here. The configuration options will likely not make sense if the preceding material is not understood.
Cascade behaviors are configured per relationship using the OnDelete method in OnModelCreating. For example:
See Relationships for more information on configuring relationships between entity types.
OnDelete accepts a value from the, admittedly confusing, DeleteBehavior enum. This enum defines both the behavior of EF Core on tracked entities, and the configuration of cascade delete in the database when EF is used to create the schema.
Impact on database schema
The following table shows the result of each OnDelete value on the foreign key constraint created by EF Core migrations or EnsureCreated.
DeleteBehavior
Impact on database schema
Cascade
ON DELETE CASCADE
Restrict
ON DELETE RESTRICT
NoAction
database default
SetNull
ON DELETE SET NULL
ClientSetNull
database default
ClientCascade
database default
ClientNoAction
database default
The behaviors of ON DELETE NO ACTION (the database default) and ON DELETE RESTRICT in relational databases are typically either identical or very similar. Despite what NO ACTION may imply, both of these options cause referential constraints to be enforced. The difference, when there is one, is when the database checks the constraints. Check your database documentation for the specific differences between ON DELETE NO ACTION and ON DELETE RESTRICT on your database system.
SQL Server doesn't support ON DELETE RESTRICT, so ON DELETE NO ACTION is used instead.
The only values that will cause cascading behaviors on the database are Cascade and SetNull. All other values will configure the database to not cascade any changes.
Impact on SaveChanges behavior
The tables in the following sections cover what happens to dependent/child entities when the principal/parent is deleted, or its relationship to the dependent/child entities is severed. Each table covers one of:
Optional (nullable FK) and required (non-nullable FK) relationships
When dependents/children are loaded and tracked by the DbContext and when they exist only in the database
Required relationship with dependents/children loaded
DeleteBehavior
On deleting principal/parent
On severing from principal/parent
Cascade
Dependents deleted by EF Core
Dependents deleted by EF Core
Restrict
InvalidOperationException
InvalidOperationException
NoAction
InvalidOperationException
InvalidOperationException
SetNull
SqlException on creating database
SqlException on creating database
ClientSetNull
InvalidOperationException
InvalidOperationException
ClientCascade
Dependents deleted by EF Core
Dependents deleted by EF Core
ClientNoAction
DbUpdateException
InvalidOperationException
Notes:
The default for required relationships like this is Cascade.
Using anything other than cascade delete for required relationships will result in an exception when SaveChanges is called.
Typically, this is an InvalidOperationException from EF Core since the invalid state is detected in the loaded children/dependents.
ClientNoAction forces EF Core to not check fixup dependents before sending them to the database, so in this case the database throws an exception, which is then wrapped in a DbUpdateException by SaveChanges.
SetNull is rejected when creating the database since the foreign key column is not nullable.
Since dependents/children are loaded, they are always deleted by EF Core, and never left for the database to delete.
Required relationship with dependents/children not loaded
DeleteBehavior
On deleting principal/parent
On severing from principal/parent
Cascade
Dependents deleted by database
N/A
Restrict
DbUpdateException
N/A
NoAction
DbUpdateException
N/A
SetNull
SqlException on creating database
N/A
ClientSetNull
DbUpdateException
N/A
ClientCascade
DbUpdateException
N/A
ClientNoAction
DbUpdateException
N/A
Notes:
Severing a relationship is not valid here since the dependents/children are not loaded.
The default for required relationships like this is Cascade.
Using anything other than cascade delete for required relationships will result in an exception when SaveChanges is called.
Typically, this is a DbUpdateException because the dependents/children are not loaded, and hence the invalid state can only be detected by the database. SaveChanges then wraps the database exception in a DbUpdateException.
SetNull is rejected when creating the database since the foreign key column is not nullable.
Optional relationship with dependents/children loaded
DeleteBehavior
On deleting principal/parent
On severing from principal/parent
Cascade
Dependents deleted by EF Core
Dependents deleted by EF Core
Restrict
Dependent FKs set to null by EF Core
Dependent FKs set to null by EF Core
NoAction
Dependent FKs set to null by EF Core
Dependent FKs set to null by EF Core
SetNull
Dependent FKs set to null by EF Core
Dependent FKs set to null by EF Core
ClientSetNull
Dependent FKs set to null by EF Core
Dependent FKs set to null by EF Core
ClientCascade
Dependents deleted by EF Core
Dependents deleted by EF Core
ClientNoAction
DbUpdateException
Dependent FKs set to null by EF Core
Notes:
The default for optional relationships like this is ClientSetNull.
Dependents/children are never deleted unless Cascade or ClientCascade are configured.
All other values cause the dependent FKs to be set to null by EF Core...
...except ClientNoAction which tells EF Core not to touch the foreign keys of dependents/children when the principal/parent is deleted. The database therefore throws an exception, which is wrapped as a DbUpdateException by SaveChanges.
Optional relationship with dependents/children not loaded
DeleteBehavior
On deleting principal/parent
On severing from principal/parent
Cascade
Dependents deleted by database
N/A
Restrict
DbUpdateException
N/A
NoAction
DbUpdateException
N/A
SetNull
Dependent FKs set to null by database
N/A
ClientSetNull
DbUpdateException
N/A
ClientCascade
DbUpdateException
N/A
ClientNoAction
DbUpdateException
N/A
Notes:
Severing a relationship is not valid here since the dependents/children are not loaded.
The default for optional relationships like this is ClientSetNull.
Dependents/children must be loaded to avoid a database exception unless the database has been configured to cascade either deletes or nulls.
Collaborate with us on GitHub
The source for this content can be found on GitHub, where you can also create and review issues and pull requests. For more information, see our contributor guide.
.NET feedback
.NET is an open source project. Select a link to provide feedback:
This module guides you through the steps to create a data access project. You connect to a relational database and construct create, read, update, and delete (CRUD) queries by using Entity Framework Core (EF Core).