EF Core Cascade delete performance improvement

EF Core: Client Cascade Delete vs Spider Source Generator Cascade Delete

Conversation with Microsoft Principal Software Engineer Shay Rojansky on the topic: https://lnkd.in/dUSuMDa8

If you've worked with EF Core and SQL Server, you’ve probably quickly encountered the error:
"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." (https://lnkd.in/dSWdG9GA)

To have a general solution and avoid constantly thinking about whether to use ClientCascadeDelete or CascadeDelete, I decided to always use ClientCascadeDelete.

After running a few queries, I noticed strange SQL being generated for ClientCascadeDelete. In my case, instead of a query like:

DELETE FROM Comment  
WHERE Id IN (1, 2, ..., 100);

it generates 100 times:

DELETE FROM Comment  
OUTPUT 1  
WHERE Id = 1;

In the Spider Framework, I created a general solution using recursion and custom attributes. The entire SpiderCascadeDelete method is generated for each entity—you just need to call the Delete{EntityName} method and the entity will be deleted correctly.

An additional performance benefit is that the SpiderCascadeDelete method only loads IDs and does so without tracking entity state (.AsNoTracking()).

Two things to watch out for when using the SpiderCascadeDelete method:

1. Too many IDs in the IN operator (if it exceeds several tens of thousands, deletion needs to be done in batches).

2. If you need entity state tracking during deletion, you cannot use the SpiderCascadeDelete method—you’ll need to manually traverse the entities, or use ClientCascadeDelete.

Source code for the benchmark using the BenchmarkDotNet library: https://lnkd.in/d8Ky3AHZ