Cascading Deletes, EF Core and SQL Server - Oh, My!

I'm documenting a problem that lives at the unique intersection of a number of development patterns. Mostly this is for myself because I always forget this kind of stuff.

Here's the list:

1. Microsoft SQL Server - latest edition
2. Entity Framework Core - latest edition (8)
3. Value objects as required foreign keys
4. Many to many relationships where both parties are required
5. Configuring EF Core for cascading deletes.

The problem

In my case I have a Calendar entity. I also have a WorkFlowTemplate which contains a list of CalendarFilter.

In essence, a CalendarFilterentity references only one Calendar and only one WorkFlowTemplate making it a child of both.

If I attempt to delete a WorkFlowTemplate by extension I must also delete all the CalendarFilterentities that belong to it. This is called cascading deletes and it's what relational databases excel at managing.

But Microsoft SQL Server doesn't do this easily for many-to-many relationships. When configuring EF Core to define the database the temptation is to allow the database to handle it, as you would for a one-to-many relationship. But for many-to-many an exception is thrown during the update of the database (but not during the migration phase).

There are two ways of handling this per EF Core documentation. One of them is to make the navigation properties nullable.

Unfortunately, I went to some lengths to make sure that property would always be valid by creating a value object, in this case a WorkFlowTemplateId:

public interface IIdValue
{
  public Guid IdValue { get; init; }
}

public record WorkFlowTemplateId(Guid IdValue) : IIdValue;

.Property(s => s.WorkFlowTemplateId)
.HasConversion(v => v.IdValue, v => new WorkFlowTemplateId(v));

One of the benefits of doing this is to create a 'guard clause' function that catches a null or Guid.Empty value in the WorkFlowTemplateId - improving the error handling pretty much everywhere.

I tried making this property nullable but things got ugly fast.

The second option was to tell EF Core that cascading deletes should be handled by the client and not the database. This seems to negate some of the benefits of using a relational database and it may not apply to all database providers, but it does to MS SQL Server. And the decision to use MS SQL Server was made long ago.

builder
  .HasOne(cf => cf.WorkFlowTemplate)
  .WithMany(template => template.CalendarFilters)
  .HasForeignKey(x => x.WorkFlowTemplateId)
  .OnDelete(DeleteBehavior.ClientCascade);


builder
  .HasOne(cf => cf.Calendar)
  .WithMany(x => x.CalendarFilters)
  .HasForeignKey(x => x.CategoryId)
  .OnDelete(DeleteBehavior.ClientCascade);

As you can see above, the OnDelete(DeleteBehavior.ClientCascade) behavior tells the database to not define cascading deletes but tells EF Core (the 'client' here) to handle it instead.

The Gotcha

If cascading deletes could be configured in the database, simply deleting the parent entity would delete all the children. The client code deleting the parent wouldn't have to know or care about any children - they would just get deleted by virtue of the relationship definition. And that happens inside the database engine and is guaranteed.

The requirement now is that EF Core must load all the related child entities and their parents and be tracking them. That means using include on the children and not using the AsNoTracking option. If this isn't set correctly the database will throw an exception because you'd be orphaning child entities, and their foreign key relationship are required.

In this particular case, EF Core tracking allowed me to have a WorkFlowTemplate clear its list of CalendarFilter without worrying about managing the Calendar. Both the relationships just get cleared and EF Core instructed the database to delete all the children.

For me this means that inside my code, I'm not looping over a list of child entities in one object and sending messages to another to break the connection, EF Core just does it for me.

It's as close to have the database cascade the deletes for me as I'm going to get and while it's not as performant or as guaranteed as have the database do it, I'll take it.

Where this really breaks down is when the collection of child entities is really large. At that point you'd be dragging potentially millions of records into memory and having EF Core track them. That would probably suck purty hard. You might have to use a stored procedure to get past that and keep the traffic in the server.