The D in CRUD

Deleting data from the database is a ubitiquous operation in almost any software application out there. The traditional approach for deleting data is using DELETE query, but it comes with a catch: There is no undo button in databases for deleted records (unless you have a backup strategy in place). This is how we normally delete a record from a relational database:

DELETE FROM [dbo].[Users]
  WHERE [Users].[UserId] = 198943;

This is known as Hard Delete, which is known to be a destructive operations since it is physically deleting the data from the database. This approach works fine for small scale traditional applications but for corporate giants, losing data is an unaffordable scenario.

Problems with Hard Delete:

• Data Loss:
  • It is very easy to specify an incorrect Id or no Id at all for the record to be deleted. This will result in irreversible data loss.
• Performance Impact & Referential Integrity:
  • In the above query, deleting a User record means we have to cascade delete on all of the data associated with that user i.e. the reviews, purchase history, wishlist e.t.c. In system with high transactions, such as a bank, this could be problematic as the database will hold lock on each record to delete it causing performance penalty.
• Legal Requirements
  • Certain countries have to abide by the data retention and data disposal procedures defined by the government or Internal governing bodies (EU). Thus, hard delete could cause penalities for non-compliance.

Soft Delete to the Rescue

Soft Delete does not use a DELETE query but rather an UPDATE query to signify the deletion of a record. This is possible because of an additional column in each database entity named IsDeleted (bit). Since we're not deleting anything from the database, this operation is Non-Destructive, so the delete operation would look like this:

UPDATE [dbo].[Users]
  SET [dbo].[Users].[IsDeleted] = 1
  WHERE [Users].[UserId] = 198943;

This is equivalent to saying: Hey remember that user with that specific ID, let's just pretend he doesn't exist anymore.

This approach eliminates all of the problems listed above, but will need some refactoring on the application layer so that deleted records do not show up in Read queries.

Soft Delete in Entity Framework Core

Let's do a deep dive into how to implement Soft Delete in EF Core:

1. Define the interface

  public interface ISoftDeletable
  {
    bool IsDeleted { get; set; }
  }

2. Implement the Interface

Any Domain Entity that supports Soft Delete would have to implement this interface so that we can later check to see if we need to Soft Delete the record or Hard Delete it.

public class AppUser : ISoftDeletable
{
  [Key]
  [DatabaseGenerated(DatabaseGenerated.Identity)]
  public uint64_t UserId { get; set; }

  [Required]
  public string FirstName { get; set; } = string.Empty;

  [Required]
  public string LastName { get; set; } = string.Empty;

  [Required]
  public string Email { get; set; } = string.Empty;

  bool IsDeleted { get; set; }
}

3. Add an Interceptor for Soft Deletable Entities

Interceptors are a powerful way to intercept requests to database. It's a way to tap into what Entity Framework is asking the database to do and change it the way we want. Let's create an Interceptor to Soft Delete entities that support Soft Deletion:

public sealed class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        if (eventData.Context is null)
        {
            return base.SavingChangesAsync(
                eventData, result, cancellationToken);
        }

        IEnumerable<EntityEntry<ISoftDeletable>> entries =
            eventData
                .Context
                .ChangeTracker
                .Entries<ISoftDeletable>()
                .Where(e => e.State == EntityState.Deleted);

        foreach (EntityEntry<ISoftDeletable> softDeletableEntity in entries)
        {
            softDeletableEntity.State = EntityState.Modified;
            softDeletableEntity.Entity.IsDeleted = true;
        }

        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }
}

4. Register the SoftDeletable Interceptor

Let's register the Interceptor as a singleton service into DI Container

builder.services.AddSingleton<SoftDeleteInterceptor>();

5. Configure DbContext to use the Interceptor

builder.services.AddDbContext<AppDbContext>(
    (serviceProvider, options) => options
        .UseSqlServer(connectionString)
        .AddInterceptors(
            serviceProvider
              .GetRequiredService<SoftDeleteInterceptor>()
          )
    );

And that's it, we've successfully configured Soft Delete in our application.

But Wait! Aren't we missing something?

If you read the article carefully enough, you'd remember the line:

Hey remember that user with that specific ID, let's just pretend he doesn't exist anymore.

This is problematic because the data is not physically deleted and when we ask for all the records for a certain entity (that supports Soft Delete) it would return us the Soft Deleted records as well, which we don't want. So how do we tackle this problem?

Let's just add a Query Filter in our DbContext to exclude the Soft Deleted records:

public class AppDbContext(
    DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<AppUser> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AppUser>().HasQueryFilter(u => !u.IsDeleted);
    }
}

And that's it. We have successfully configured our App to use Soft Deletion.