Dotnet logo

.NET Tools

Essential productivity kit for .NET and game developers

.NET Tools

How to Implement a Soft Delete Strategy with Entity Framework Core

I’m sure we’ve all had a turbulent relationship with the definition of “delete” throughout our development careers. So what does the user mean when they say “delete my data”? Well, if you’re anything like me, you’ll quickly realize the user wants to logically delete information from cluttering the user interface and not permanently delete records out of existence… Oops 😬. 

With hard lessons learned, many developers will turn to a Soft Delete strategy allowing them to reverse accidental deletes, maintain data integrity, and general administrative oversight. You may also be required by law to retain data for a certain period, and this strategy can help you accomplish those requirements.

This post will explore how to implement a Soft Delete strategy with Entity Framework Core and how to use it during writing and reading to your database engine of choice.

What is a Soft Delete?

As alluded to in the introduction, there are two kinds of deletes in application development: physical and logical. 

A physical delete removes a record from a data store and is highly destructive. Data deleted through a physical delete is lost, and only system administrators can recover the data, typically using extreme measures or backups. Physical deletes commonly use a mechanism of the data storage engine to execute a non-reversible command. For example, SQL-based databases can run DELETE statements to remove records from a table (hands up if you’ve ever accidentally forgotten the WHERE clause).

DELETE FROM dbo.Movies
WHERE Movies.Id = '1'

Conversely, a soft delete is a logical decision made by the development team to mark records to ignore during queries. Root elements of a data model will have a flag of some kind, either a boolean flag or a timestamp indicating the time of deletion. Queries applied to root elements must explicitly specify whether to use the deletion indicator as a factor in producing a result set. 

For example, here’s a SQL query returning a record, but only if the IsDeleted bit column is set to 0 for “false”.

SELECT * FROM dbo.Movies
WHERE Movies.Id = '1' AND Movies.IsDeleted = 0

If you or your user would like to recover data, recovering deleted data is as straightforward as changing the value of the deletion indicator.

UPDATE Movies
SET Movies.IsDeleted = 0
WHERE Id = '1';

Soft delete markers are more challenging to implement into an existing system, as it takes some thought about when and where to apply deletion indicators. Additionally, there can be some overhead in the form of additional indexes and a growing record count. These are drawbacks worth considering if you have limited disk space or I/O limitations.

Now that you have a general idea of what constitutes a Soft Delete strategy let’s go ahead and implement it using Entity Framework Core.

Entity Framework soft deletes with Interceptors

Entity Framework Core includes a concept of interceptors – an approach to extending the execution pipeline. There are several types of interceptors, and standard implementations allow you to modify the SQL command, alter entities before you save any changes, and use auditing techniques.

In this example, you’ll use an interceptor to modify entities during the writing phase of the application. First, let’s define a Movie entity, which you will adjust to support soft deletes.

public class Movie 
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public string Writer { get; set; } = "";
    public string Director { get; set; } = "";
    public int ReleaseYear { get; set; }
    
    public override string ToString()
        => $"{Id}: {Title} ({ReleaseYear})";
}

To allow for increased reuse, we’ll create an ISoftDelete interface, giving you shared properties and implementation to undo any delete. Any of the properties, IsDeleted or DeletedAt, is sufficient for a soft delete strategy, but I’ve added both in this example for maximum verbosity. If you want to adopt this soft-delete approach, you’’ probably only want to have one of these properties. 

public interface ISoftDelete
{
    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }

    public void Undo()
    {
        IsDeleted = false;
        DeletedAt = null;
    }
}

Apply the interface to the Movie entity, and look at the final entity definition.

public class Movie : ISoftDelete
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public string Writer { get; set; } = "";
    public string Director { get; set; } = "";
    public int ReleaseYear { get; set; }
    
    public override string ToString()
        => $"{Id}: {Title} ({ReleaseYear})";

    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
}

While you could set the deleted flag on every entity you want to delete, that would be tedious and not to mention error-prone. So, let’s take advantage of EF Core infrastructure and write a SoftDeleteInterceptor.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData, 
        InterceptionResult<int> result)
    {
        if (eventData.Context is null) return result;
        
        foreach (var entry in eventData.Context.ChangeTracker.Entries())
        {
            if (entry is not { State: EntityState.Deleted, Entity: ISoftDelete delete }) continue;

            entry.State = EntityState.Modified;
            delete.IsDeleted = true;
            delete.DeletedAt = DateTimeOffset.UtcNow;
        }

        return result;
    }
}

As you call SaveChanges on a DbContext instance, this interceptor will check to see if any entry in the change tracker implements ISoftDelete. If so, the interceptor will change the entity state of Deleted to Modified and set all soft delete properties.

As you can see in the implementation, this interceptor works with EF Core constructs before invoking any database-specific functionality. This interceptor will work with any database provider supported by EF Core, including, but not limited to, SQL Server, PostgreSQL, SQLite, and MySQL. For this sample, I’ve used the Microsoft.EntityFrameworkCore.InMemory package, but feel free to substitute your favorite provider.

The final step to complete your writing phase modifications is registering the interceptor with a DbContext definition using the call to AddInterceptors during the OnConfiguring phase of initialization.

using Microsoft.EntityFrameworkCore;

public class Database : DbContext
{
    public DbSet<Movie> Movies => Set<Movie>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseInMemoryDatabase("test")
            .AddInterceptors(new SoftDeleteInterceptor());

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

Any attempt to remove an entity from the database using the EF Core DbContext will switch from a delete to an update statement. Let’s go ahead and see it in action.

var db = new Database();
var firstMovie = db.Movies.First();

Console.WriteLine($"{firstMovie.Title} ({firstMovie.ReleaseYear})");

// delete operation (actually an update)
db.Movies.Remove(firstMovie);
db.SaveChanges();
Console.WriteLine($"Deleted \"{firstMovie}\"");

As you may have noticed, the code looks like regular old EF Core. What about reading data? How do you filter out deleted records? You’ll see how to do that in the next section.

Automatically filter soft-deleted records

Marking records to be deleted is only half the story. With a single configuration, you can tell EF Core to ignore soft-deleted records when executing queries, and you can do that using query filters on our entity definitions. For example, the modified DbContext definition with a query filter on the Movies collection is here.

public class Database : DbContext
{
    public DbSet<Movie> Movies => Set<Movie>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseInMemoryDatabase("test")
            .AddInterceptors(new SoftDeleteInterceptor());

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Automatically adding query filter to 
        // all LINQ queries that use Movie
        modelBuilder.Entity<Movie>()
            .HasQueryFilter(x => x.IsDeleted == false);
    }
}

You can apply as many query filters as you like, but I suggest limiting query filters to what’s necessary, as they are typically invisible in a LINQ query. The “invisible” nature of query filters means developers on your team have to understand and manage these concepts in their minds. With too many filters, it can get confusing and lead to unexpected bugs.

Putting it all together with reads and writes

I’ve created a quick sample application below, something you might expect to see given the DbContext from previous sections.

using Microsoft.EntityFrameworkCore;
using SoftDeletes.Models;

// save test data of movies
Movies.Initialize();

var db = new Database();
var firstMovie = db.Movies.First();

Console.WriteLine($"{firstMovie.Title} ({firstMovie.ReleaseYear})");

// delete operation
db.Movies.Remove(firstMovie);
db.SaveChanges();
Console.WriteLine($"Deleted \"{firstMovie}\"");

Console.WriteLine($"Total Movies: {db.Movies.Count()}");
Console.WriteLine($"Total Movies (including deleted): {db.Movies.IgnoreQueryFilters().Count()}");
Console.WriteLine($"Total Deleted: {db.Movies.IgnoreQueryFilters().Count(x => x.IsDeleted)}");

public static class Movies
{
    public static readonly IReadOnlyList<Movie> All = new List<Movie> {
        new() { Id = 1, Title = "Glass Onion", Director = "Rian Johnson", Writer = "Rian Johnson", ReleaseYear = 2022 },
        new() { Id = 2, Title = "Avatar: The Way of Water", Director ="James Cameron", Writer = "James Cameron", ReleaseYear = 2022 },
        new() { Id = 3, Title = "The Shawshank Redemption", Writer = "Stephen King", Director = "Frank Darabont", ReleaseYear = 1994 },
        new() { Id = 4, Title = "Pulp Fiction", Writer = "Quentin Tarantino", Director = "Quentin Tarantino", ReleaseYear = 1994 },
        new() { Id = 5, Title = "Seven Samurai", Writer = "Akira Kurosawa", Director = "Akira Kurosawa", ReleaseYear = 1954 },
        new() { Id = 6, Title = "Gladiator", Writer = "David Franzoni", Director = "Ridley Scott", ReleaseYear = 2000 },
        new() { Id = 7, Title = "Old Boy", Writer = "Garon Tsuchiya", Director = "Park Chan-wook", ReleaseYear = 2003 },
        new() { Id = 8, Title = "A Clockwork Orange", Director = "Stanley Kubrick", Writer = "Stanley Kubrick", ReleaseYear = 1971 },
        new() { Id = 9, Title = "Metroplis", Director = "Fritz Lang", Writer = "Thea von Harbou", ReleaseYear = 1927 },
        new() { Id = 10, Title = "The Thing", Director = "John Carpenter", Writer = "Bill Lancaster", ReleaseYear = 1982 }
    };
    
    public static void Initialize()
    {
        var db = new Database();
        db.Movies.AddRange(All);
        db.SaveChanges();
    }
}

You’ll notice no mention of the IsDeleted flag anywhere in the code. The lack of ISoftDelete properties in read/write usage is because the EF Core interceptor and query filter use the properties transparently.

Additionally, to negate query filters, you can use IgnoreQueryFilters on any LINQ query, and you’ll get unadulterated access to form a LINQ query.

// after a delete
db.Movies.Count(); // 9
db.Movies.IgnoreQueryFilters().Count(); // 10

Let’s take a look at the complete application in a single file.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

// save test data of movies
Movies.Initialize();

var db = new Database();
var firstMovie = db.Movies.First();

Console.WriteLine($"{firstMovie.Title} ({firstMovie.ReleaseYear})");

// delete operation
db.Movies.Remove(firstMovie);
db.SaveChanges();
Console.WriteLine($"Deleted \"{firstMovie}\"");

Console.WriteLine($"Total Movies: {db.Movies.Count()}");
Console.WriteLine($"Total Movies (including deleted): {db.Movies.IgnoreQueryFilters().Count()}");
Console.WriteLine($"Total Deleted: {db.Movies.IgnoreQueryFilters().Count(x => x.IsDeleted)}");

public static class Movies
{
    public static readonly IReadOnlyList<Movie> All = new List<Movie> {
        new() { Id = 1, Title = "Glass Onion", Director = "Rian Johnson", Writer = "Rian Johnson", ReleaseYear = 2022 },
        new() { Id = 2, Title = "Avatar: The Way of Water", Director ="James Cameron", Writer = "James Cameron", ReleaseYear = 2022 },
        new() { Id = 3, Title = "The Shawshank Redemption", Writer = "Stephen King", Director = "Frank Darabont", ReleaseYear = 1994 },
        new() { Id = 4, Title = "Pulp Fiction", Writer = "Quentin Tarantino", Director = "Quentin Tarantino", ReleaseYear = 1994 },
        new() { Id = 5, Title = "Seven Samurai", Writer = "Akira Kurosawa", Director = "Akira Kurosawa", ReleaseYear = 1954 },
        new() { Id = 6, Title = "Gladiator", Writer = "David Franzoni", Director = "Ridley Scott", ReleaseYear = 2000 },
        new() { Id = 7, Title = "Old Boy", Writer = "Garon Tsuchiya", Director = "Park Chan-wook", ReleaseYear = 2003 },
        new() { Id = 8, Title = "A Clockwork Orange", Director = "Stanley Kubrick", Writer = "Stanley Kubrick", ReleaseYear = 1971 },
        new() { Id = 9, Title = "Metroplis", Director = "Fritz Lang", Writer = "Thea von Harbou", ReleaseYear = 1927 },
        new() { Id = 10, Title = "The Thing", Director = "John Carpenter", Writer = "Bill Lancaster", ReleaseYear = 1982 }
    };
    
    public static void Initialize()
    {
        var db = new Database();
        db.Movies.AddRange(All);
        db.SaveChanges();
    }
}

public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData, 
        InterceptionResult<int> result)
    {
        if (eventData.Context is null) return result;
        
        foreach (var entry in eventData.Context.ChangeTracker.Entries())
        {
            if (entry is not { State: EntityState.Deleted, Entity: ISoftDelete delete }) continue;

            entry.State = EntityState.Modified;
            delete.IsDeleted = true;
            delete.DeletedAt = DateTimeOffset.UtcNow;
        }

        return result;
    }
}

public class Database : DbContext
{
    public DbSet<Movie> Movies => Set<Movie>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseInMemoryDatabase("test")
            .AddInterceptors(new SoftDeleteInterceptor());

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Movie>()
            .HasQueryFilter(x => x.IsDeleted == false);
    }
}

public class Movie : ISoftDelete
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public string Writer { get; set; } = "";
    public string Director { get; set; } = "";
    public int ReleaseYear { get; set; }
    
    public override string ToString()
        => $"{Id}: {Title} ({ReleaseYear})";

    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
}

public interface ISoftDelete
{
    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }

    public void Undo()
    {
        IsDeleted = false;
        DeletedAt = null;
    }
}

Executing the program above will give you the following output.

Glass Onion (2022)
Deleted "1: Glass Onion (2022)"
Total Movies: 9
Total Movies (including deleted): 10
Total Deleted: 1

So straightforward and much easier to accomplish now with EF Core than in previous iterations. This a reminder that no data is physically deleted, only logically “deleted”. The code to insert/delete entities remains the same, and querying also does not look any different from routine EF queries. All thanks to the power of EF interceptors.

Conclusion

The soft delete strategy can help you provide the user experiences you intend without the risk of catastrophically destroying data. While the technique has some overhead, you can overcome these challenges using EF Core infrastructure and a simple interface. You’ll likely want to add additional indexes to your tables for the deletion flags to speed up queries. In addition, you can apply query filters and interceptors to other problems. It is good knowledge when looking at different approaches to solving complex business tasks.

Thank you for reading, and if you have any comments or questions, please feel free to leave them in the comment section.

Image Credit: Alex Rybin

image description