Entity Framework Logical Delete Part Four: Automating using stored procedures

In the last post of the series, I identified a good enough solution to handle the EF soft delete scenario with good performance and no abstraction leaking. The solution itself contains a well-defined process, but ideally this process should be automatized as much as possible. Just a reminder about the steps:

Map the CUD operations using the .MapToStoredProcedures() method.
Add an IsDeleted column to every entity table where the represented entity has the custom annotation.
Modify the stored procedure to do the update.

Defining a way to identify soft deletable entities

First thing's first: we have to define a way so that during SQL-building, the entities that require soft delete can be identified. The possible options off the top of my head can be:

Using an attribute. Adding this attribute to a class would denote the soft-delete capability. I don't like it: if you've read any of my blog posts about EF, I'm not a fan of adding DB-related information to the domain model (and if you haven't read any of my other EF related posts, you should :) ).
Using an interface. Again, I don't really like it — after all, I made a big deal about the abstraction leaking in the previous posts.
All that's left is a custom annotation. This is actually quite good I think, so that's the winner.

Adding a custom annotation is as easy as adding a simple line of code in the .OnModelCreating() method of the context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Person>().HasTableAnnotation("SoftDelete", true); 
}

Also, you have to map the entity CUD stored procedures. You can wrap the two things into a neat little extension method like this:

public static class SoftDeleteExtensions
{
  public static EntityTypeConfiguration<T> UseSoftDelete<T>(this EntityTypeConfiguration<T> config) where T:class
  {
    config.HasTableAnnotation("SoftDelete", true);
    config.MapToStoredProcedures();
    return config;
  }
}

And then use it in your context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Person>().UseSoftDelete();
}

Awesome. Next up we have to find a way to instruct EF to generate an extra column for the IsDeleted property in the tables that have the custom annotation and also modify the generated delete stored procedure for these entities.

Creating a custom SQL generator

EF has a more-or-less pluggable architecture and the behavior of the components can be overriden (again, more-or-less). If you want, you can create a custom SQL generator that is used when creating migrations. You have to inherit the SqlServerMigrationSqlGenerator class, which has an overridable method for most SQL statement generation. You can override the table generating operation to include the extra column for IsDeleted if the entity has the custom table annotation like this:

public class SoftDeleteMigrator : SqlServerMigrationSqlGenerator
{       
  protected override void Generate(CreateTableOperation createTableOperation)
  {
    if (createTableOperation.Annotations.TryGetValue("SoftDelete", out object hasSoftDeleteObject))
    {
      if (bool.TryParse(hasSoftDeleteObject.ToString(), out bool hasSoftDeleteResult) && hasSoftDeleteResult)
      {
         createTableOperation.Columns.Add( new ColumnModel(PrimitiveTypeKind.Boolean) { 
                                          Name = "IsDeleted", 
                                          IsNullable = false,
                                          DefaultValueSql = "0" 
                                        });   
      }
    }
    base.Generate(createTableOperation);
  }
}

That's how you do it. You have to override the Generate method that has a paramter of type CreateTableOperation — this is responsible for generating the CREATE TABLE SQL operation. You can query the custom annotation from the .Annotations property of the parameter, parse its value, and if it is present and is true, you can add a new column to the .Columns property of the parameter. Finally, simply call the base version with the modified parameter.

Then, you have to enable migrations with the Enable-Migrations command. If you enable the migration, a class named Configuration is generated. You have to register your custom SQL generator in the configuration class:

internal sealed class Configuration : DbMigrationsConfiguration<ConsoleApp1.MyCtx>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;
    this.SetSqlGenerator("System.Data.SqlClient", new SoftDeleteMigrator());            
        }
    }

And now, if you run Add-Migration, and then Update-Database or Update-Database -Script, you'll see that the extra column is added for the Person table. How awesome is that? Very. Very, very awesome.

Here's the part that is not that awesome: unfortunately I was not able to do the same thing for the delete stored procedure. There is a method that I would think should be overridden for a custom stored procedure implementation, but I couldn't get it to work (you can find the chronicle of my struggles on StackOverflow). So for now, you have to generate the migration and modify the body of the SP yourself. On one hand, this is not that big of a deal, because for cascade deletes, you'd have to modify it anyways. On the other hand, it is a big deal, because for the cascading scenario, you could introduce another method of indicating the fact of cascading delete, and generate the SP accordingly. But I would like to point out that this is a shortcoming of my technical knowledge and not the design of the solution. If anyone has any input on how to do this, check out the Github repo (in the last post of the series, there is one), answer the SO question above, or just leave a comment.

On the other-other hand, there is a solution that could be used to automate cascade soft deletion even further. I don't really like it, there are some design flaws in it IMHO, but since I've struggled through it and it is interesting, I'm gonna post about it anyways :) Stay tuned!