Microsoft SQL Server has a little-known and little-used feature called cascade update. Just like cascade delete, which deletes the referring records along a FK, the cascade update feature updates the values of the FK in the referring entities if the referred value changes. So let's say you have a table called Person, with its primary key being PersonId. And then you have a Car table, which has a PersonId foreign key to the Person table. With cascade update, if you update the PersonId in Person, the value of PersonId in the referring Car records are also updated to keep the relationship intact. (Do you see now why it is used so rarely? Why would you ever update the PK in Person at the first place?).

This is the feature that will help us to achieve cascade soft delete. (Of course, we could use triggers as well, but c'mon: triggers in the 21st century? Only over my dead body). We need to update the IsDeleted value in the referring entities when it is changed in the referred entity.

So let's say that you have a Car entity that refers to the Person entity via the PersonId foreign key, and both of them are soft delete entities.

First, create the Car entity and add it to the context and the Person type:

public class Car
{
  public int CarId { get; set; }
  public string LicensePlate { get; set; }
  public int PersonId { get; set; }
  public virtual Person Person { get; set; }
}

public class Person
{
  public Person()
  {
    Cars = new HashSet<Car>();
  }
  public int PersonId { get; set; }
  public string Name { get; set; }
  public int Age { get; set; }
  public virtual ICollection<Car> Cars { get; set; }
}

public class PersonTestContext : DbContext
{
  public PersonTestContext() : base("name=MTest") { }
  public DbSet<Person> People { get; set; }
  public DbSet<Car> Cars { get; set; }
  
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Person>().UseSoftDelete();
    modelBuilder.Entity<Car>().UseSoftDelete();
  }
}

Next up, create the enable the migration, set the sql generator (as discussed in the last post) and then generate the database. Finally, modify the delete SP for the Person table (again, as discussed in the another post, part three).
And now, let's dive in :)

Implementing cascade logical delete with cascade update

First, you have to modify the Person table. Currently, it's primary key if the PersonId column; you have to change it so it becomes a composite key of (PersonId, IsDeleted). Also, specify a default false (0 for bit) value for the property. You can simply use the designer of SQL Server Management Studio to do this. If you use designer, it also automatically deletes the original FK from Car to Person.

If you're done, you can use the follwing query to create a composite FK from Car to Person and specify the cascading update nature of the relationship:

ALTER TABLE [dbo].[Cars] ADD CONSTRAINT [FK_dbo.Cars_dbo.People] FOREIGN KEY ([PersonId], [IsDeleted]) 
REFERENCES [dbo].[People] ([PersonId], [IsDeleted]) ON UPDATE CASCADE

That's it. You don't have to modify the model (in fact, you shouldn't; just imagine how much abstraction leaking that would mean!!). You can go ahead and insert your person records with their car records. And then go ahead and delete the person record. Lo and behold: the car record also has its IsDeleted set to 1 automatically. Since the IsDeleted in Person is part of the PK and the PK is modified, all referring FKs where ON UPDATE CASCADE is specified get updated — meaning the IsDeleted in the referring Car records.

This is a very elegant solution, but it has a major drawback. Since the FK in Car is now (PersonId, IsDeleted), the relationship is defined by both of these columns, and they have to refer to a valid Person record together. So if you set IsDeleted to 1 in a Car record, this means that the referred Person record must also have 1 in the IsDeleted column, otherwise the relationship would break. Or in other words, you cannot apply soft delete to a Car record only, you have to soft delete the owner Person as well (or really delete the Car record).

So that's it people: these are my options for implementing soft delete using EF. I have to admit that I don't really like the solution discussed in this post because of that one major drawback. I think implementing cascade soft delete in the delete SP is a small price to pay for the added (and usually required) flexibility.

Now don't worry, the series is far from over. I still haven't discussed how to filter the deleted entities when issuing queries for the database. I'll discuss my options in the next few posts :) Stay tuned!