Handling Cascading Deletes in Entity Framework Core

Cascading deletes in Entity Framework Core (EF Core) simplify data management by automatically removing related entities when their parent entities are deleted. However, when multiple paths exist between parent and child entities, cascading delete conflicts occur which then pose a challenge for maintaining data integrity.

Scenario

Let’s look at an example Entity Framework Core model which includes tables for a Department, Employee, Project and Task. An Employee belongs to a Department, so it has a relationship to a Department, and a Project also belongs to a Department, so it will also have a relationship to a Department. A Task belongs to an Employee and a Project, so it will have a relationship to both an Employee and a Project.

This will result in a table structure which looks like the diagram below.

Setup in Entity Framework Core

While setting this type of scenario up in Entity Framework Core, you will encounter the following error message when running your database migrations using the Update-Database or dotnet ef database update commands.

Introducing FOREIGN KEY constraint 'FK_Tasks_Projects_ProjectId' on table 'Tasks' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint or index.

The reason for this is that Microsoft SQL Server does not allow multiple cascade paths to exist on one table. As a result, this error is returned when the database migrations are attempted to be executed.

Managing Cascading Deletes in Entity Framework Core

There are a few different ways to handle this situation.

1. Make one or more of the relationships optional so that cascade deletion is no longer required.
2. Use the DeleteBehavior.ClientCascade option so that EF Core will delete the entities when the parent entity is deleted.
3. Create SQL triggers to handle the deletion of related entities.

Each of these options has different pros and cons, so there is no definitive solution, and the best fit will depend on each case. Making the relationship optional might not be appropriate for your model so that may not be a good option, while it is the easiest to do.

The second option is to use the DeleteBehavior.ClientCascade configuration to make EF Core responsible for deleting related entities. This will preserve the required relationship in the model and database; however, it is necessary to load the related child entities with EF Core so that they are tracked, otherwise they will not be automatically deleted when the parent entity is deleted.

modelBuilder.Entity<Task>()
    .HasOne(t => t.Employee)
    .WithMany(e => e.Tasks)
    .HasForeignKey(t => t.EmployeeId)
    .OnDelete(DeleteBehavior.ClientCascade);

modelBuilder.Entity<Task>()
    .HasOne(t => t.Project)
    .WithMany(p => p.Tasks)
    .HasForeignKey(t => t.ProjectId)
    .OnDelete(DeleteBehavior.ClientCascade);

The third option is to use SQL Triggers to delete the entities instead. This will preserve the required relationships in the EF Core model and allow SQL Server to manage to removal of related child entities. However, EF Core does not contain the tooling to manage SQL Triggers so these will need to be created and maintained manually (although this can be done through EF Core migrations).

Using SQL Triggers

To setup SQL triggers the first step is to remove the cascade delete action from the constraint which Entity Framework Core creates. This can be done by using the DeleteBehavior.NoAction option on every relationship for the tables involved. As of EF Core 7, it is also necessary to tell EF Core that triggers are used on the tables so that it can construct database queries appropriately. Both can be done with the following entity configuration.

modelBuilder.Entity<Employee>()
    .HasOne(e => e.Department)
    .WithMany(d => d.Employees)
    .HasForeignKey(e => e.DepartmentId)
    .OnDelete(DeleteBehavior.NoAction);

modelBuilder.Entity<Project>()
    .HasOne(p => p.Department)
    .WithMany(d => d.Projects)
    .HasForeignKey(p => p.DepartmentId)
    .OnDelete(DeleteBehavior.NoAction);

modelBuilder.Entity<Task>()
    .HasOne(t => t.Employee)
    .WithMany(e => e.Tasks)
    .HasForeignKey(t => t.EmployeeId)
    .OnDelete(DeleteBehavior.NoAction);

modelBuilder.Entity<Task>()
    .HasOne(t => t.Project)
    .WithMany(p => p.Tasks)
    .HasForeignKey(t => t.ProjectId)
    .OnDelete(DeleteBehavior.NoAction);
modelBuilder.Entity<Department>().ToTable(d => d.HasTrigger("[Department_Delete]"));

modelBuilder.Entity<Employee>().ToTable(e => e.HasTrigger("[Employee_Delete]"));

modelBuilder.Entity<Project>().ToTable(p => p.HasTrigger("[Project_Delete]"));

modelBuilder.Entity<Task>().ToTable(t => t.HasTrigger("[Task_Delete]"));

The necessary triggers can be created by adding the following SQL to an EF Core migration.

migrationBuilder.Sql(@"
    CREATE TRIGGER [dbo].[Department_Delete]
    ON [dbo].[Departments]
    INSTEAD OF DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE FROM [dbo].[Projects] WHERE [DepartmentId] IN (SELECT [DepartmentId] FROM DELETED);
        DELETE FROM [dbo].[Employees] WHERE [DepartmentId] IN (SELECT [DepartmentId] FROM DELETED);
        DELETE FROM [dbo].[Departments] WHERE [DepartmentId] IN (SELECT [DepartmentId] FROM DELETED);
    END");

migrationBuilder.Sql(@"
    CREATE TRIGGER [dbo].[Employee_Delete]
    ON [dbo].[Employees]
    INSTEAD OF DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE FROM [dbo].[Tasks] WHERE [EmployeeId] IN (SELECT [EmployeeId] FROM DELETED);
        DELETE FROM [dbo].[Employees] WHERE [EmployeeId] IN (SELECT [EmployeeId] FROM DELETED);
    END");

migrationBuilder.Sql(@"
    CREATE TRIGGER [dbo].[Project_Delete]
    ON [dbo].[Projects]
    INSTEAD OF DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE FROM [dbo].[Tasks] WHERE [ProjectId] IN (SELECT [ProjectId] FROM DELETED);
        DELETE FROM [dbo].[Projects] WHERE [ProjectId] IN (SELECT [ProjectId] FROM DELETED);
    END");

The appropriate trigger will be run for each table when a DELETE statement is executed against it. Each trigger will delete the dependent rows from related tables and then remove the rows from the principal table, effectively creating the same behaviour as a cascade delete.

Severed Relationships in Entity Framework Core

One of the features of Entity Framework Core is to ability to delete dependent entities by removing them from a collection on principal entity. For example, in the code below a task is removed from the project.

var project = _context.Projects
    .Include(p => p.Tasks)
    .Single(p => p.ProjectId == 2);

var task = project.Tasks.Single(t => t.TaskId == 5);

project.Tasks.Remove(task);

This severs the relationship between the two entities, however, with the current setup the following exception is thrown.

System.InvalidOperationException: 'The association between entity types 'Project' and 'Task' has been severed, but the relationship is either marked as required or is implicitly required because the foreign key is not nullable. If the dependent/child entity should be deleted when a required relationship is severed, configure the relationship to use cascade deletes. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the key values.'

This exception will occur whenever EF Core is tracking the entity and it is attempted to be removed. The solution to this is to directly remove dependent entities through the DbContext so EF Core knows to delete them from the database.

var project = _context.Projects
    .Include(p => p.Tasks)
    .Single(p => p.ProjectId == 2);

var task = project.Tasks.Single(t => t.TaskId == 5);

_context.Tasks.Remove(task);

This will cause EF Core to generate the appropriate SQL to remove the entity. Any required cascading deletes will be handled by the SQL triggers.

Summary

Microsoft SQL Server does not support more than one cascading delete relationship, however, this is occasionally appropriate in some models. The recommended alternative to using cascading deletes is to use SQL triggers to achieve the required behaviour.

In the above, we configure EF Core to not create cascading deletes on the relationships between entities. We also configure EF Core to be aware that the tables have SQL triggers so that it generates compatible SQL. The dependent entities can then be removed through the DbContext with cascading deletes handled by the SQL triggers.