Entity Framework Core: Soft Delete using Query Filters

Note: I use the preview version of Entity Framework Core 2.0 (2.0.0-preview2-final). Things may change after the final version is released

I continue my exploration of Entity Framework Core 2.0. Today, I'll explore how to use soft-delete (or logical delete) easily. I mean implementing soft-delete in a transparency way, e.g. as if you were actually deleting rows.

To implement soft delete, you need to add a column to indicate if the row is logically deleted. You can use a boolean column, or a date column if you want know when the row was deleted. The second part is to change all queries to use this column to filter the result set. You also need to replace delete statements by update statements.

Let's see how to do this 2 things with Entity Framework Core!

Add the IsDeleted column

Entity Framework Core provides a very flexible mapping. In the previous post about tracking columns, you'll find the 3 ways to map a column. In the introduction, I said soft delete should be transparent. So I decide to not expose the IsDeleted in the classes. The model is the following:

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Now, we need to indicate Entity Framework Core that there is an additional column:

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Post>()
            .Property<bool>("IsDeleted");
    }
}

Change the insert/delete queries

Entity Framework Core stores all the changes using the ChangeTracker. You can modify the entries of the ChangeTracker just before EF generates the queries and executes them on the database.

public class BloggingContext : DbContext
{
    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        OnBeforeSaving();
        return base.SaveChanges(acceptAllChangesOnSuccess);
    }

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
        OnBeforeSaving();
        return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
    }

    private void OnBeforeSaving()
    {
        foreach (var entry in ChangeTracker.Entries<Post>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.CurrentValues["IsDeleted"] = false;
                    break;

                case EntityState.Deleted:
                    entry.State = EntityState.Modified;
                    entry.CurrentValues["IsDeleted"] = true;
                    break;
            }
        }
    }
}

Now the generated query for the following code:

using (var context = new BloggingContext())
{
    var post = new Post { Blog = blog };
    context.Posts.Add(post);
    context.SaveChanges();
}
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Posts] ([BlogId], [Content], [IsDeleted], [Title])
VALUES (@p1, @p2, @p3, @p4);
SELECT [PostId]
FROM [Posts]
WHERE @@ROWCOUNT = 1 AND [PostId] = scope_identity();
-- @p3 is 0 (false)
',N'@p1 int,@p2 nvarchar(4000),@p3 bit,@p4 nvarchar(4000)',@p1=1,@p2=NULL,@p3=0,@p4=NULL
    context.Posts.Remove(post);
    context.SaveChanges();
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Posts] SET [BlogId] = @p0, [Content] = @p1, [IsDeleted] = @p2, [Title] = @p3
WHERE [PostId] = @p4;
SELECT @@ROWCOUNT;

',N'@p4 int,@p0 int,@p1 nvarchar(4000),@p2 bit,@p3 nvarchar(4000)',@p4=1,@p0=1,@p1=NULL,@p2=1,@p3=NULL

The insert and delete requests are handled. You now have to change all the select queries.

Change the select queries

Entity Framework Core 2.0 has introduced a new concept: Query Filters. A query filter is a where clause which is always added to the generated queries. This means, you can declare the filter for an entity once during the model creation, and then, this filter is implicitly added to the generated queries that use the table.

public class BloggingContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Post>()
            .Property<bool>("IsDeleted");

        modelBuilder.Entity<Post>()
            .HasQueryFilter(post => EF.Property<bool>(post, "IsDeleted") == false);
    }
}

Let's see the query filter in action:

 var posts = context.Posts.ToList();
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[IsDeleted], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[IsDeleted] = 0 -- Query filter

The query filters are also used for including relations:

 var blogs = context.Blogs.Include(_ => _.Posts);
SELECT [_].[BlogId], [_].[Url]
FROM [Blogs] AS [_]
ORDER BY [_].[BlogId]

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[IsDeleted], [p].[Title]
FROM [Posts] AS [p]
INNER JOIN (
    SELECT [_0].[BlogId]
    FROM [Blogs] AS [_0]
) AS [t] ON [p].[BlogId] = [t].[BlogId]
WHERE [p].[IsDeleted] = 0 -- Query filter
ORDER BY [t].[BlogId]

Query filters make soft-delete implementation so easy 😃

Querying soft-deleted rows

If you want to restore deleted rows, you must be able to select them. This means, you need to remove the query filters temporary. EF has added a new method IgnoreQueryFilters to indicate you don't want query filters to be used for the current query.

var deletedPosts = context.Posts.IgnoreQueryFilters()
                    .Where(post => EF.Property<bool>(post, "IsDeleted") == true);

Restoring deleted posts is a little verbose. You need to query them and update the IsDeleted property in the change tracker.

var deletedPosts = context.Posts.IgnoreQueryFilters().Where(post => EF.Property<bool>(post, "IsDeleted") == true);
foreach (var deletedPost in deletedPosts)
{
    var postEntry = context.ChangeTracker.Entries<Post>().First(entry => entry.Entity == deletedPost);
    postEntry.Property("IsDeleted").CurrentValue = false;
}
context.SaveChanges();

Automatically apply soft-delete to all entities of the model

To apply the soft-delete to all entities of the model, you can iterate over all entity types and add the property and the query filter. The tricky part in the code is the creation of the lambda expression. Indeed, you cannot use the classic syntax because you don't know the type of the entity at compile type. So, you have to create it by hand and it requires a few more lines.

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        foreach (var entityType in builder.Model.GetEntityTypes())
        {
            // 1. Add the IsDeleted property
            entityType.GetOrAddProperty("IsDeleted", typeof(bool));

            // 2. Create the query filter

            var parameter = Expression.Parameter(entityType.ClrType);

            // EF.Property<bool>(post, "IsDeleted")
            var propertyMethodInfo = typeof(EF).GetMethod("Property").MakeGenericMethod(typeof(bool));
            var isDeletedProperty = Expression.Call(propertyMethodInfo, parameter, Expression.Constant("IsDeleted"));

            // EF.Property<bool>(post, "IsDeleted") == false
            BinaryExpression compareExpression = Expression.MakeBinary(ExpressionType.Equal, isDeletedProperty, Expression.Constant(false));

            // post => EF.Property<bool>(post, "IsDeleted") == false
            var lambda = Expression.Lambda(compareExpression, parameter);

            builder.Entity(entityType.ClrType).HasQueryFilter(lambda);
        }
    }

    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        OnBeforeSaving();
        return base.SaveChanges(acceptAllChangesOnSuccess);
    }

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
        OnBeforeSaving();
        return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
    }

    private void OnBeforeSaving()
    {
        foreach (var entry in ChangeTracker.Entries())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.CurrentValues["IsDeleted"] = false;
                    break;

                case EntityState.Deleted:
                    entry.State = EntityState.Modified;
                    entry.CurrentValues["IsDeleted"] = true;
                    break;
            }
        }
    }
}

Conclusion

Implementing the soft-delete pattern with Entity Framework Core 2.0 is very easy and can be transparent. In fact, you can add soft-delete to an existing model without changing your LINQ code.

Comments

Daniel Paiva -

Hello, Gérald Barré.

This post is genius!! Works like a charm! THANK YOU!!!

I have a question. I have MANY models. Would I have to do:

  1. Inside >
private void OnBeforeSaving()
{
    // this below //
    foreach (var entry in ChangeTracker.Entries<oneforeachmodel>())
    {
        ...
    }

    // this above //
    ...
}
  1. Inside >
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    // this below //
    modelBuilder.Entity<oneforeachmodel>()
        .Property<bool>("IsDeleted");
    modelBuilder.Entity<oneforeachmodel>()
        .HasQueryFilter(post => EF.Property<bool>(post, "IsDeleted") == false);
    // this above //
    ...
}

OR >> Do you have a better solution?

Meziantou -

Hi,

For the OnModelCreating method, I would create an extension method, so you can write modelBuilder.Entity<oneforeachmodel>().AddIsDeletedProperty()

For the OnBeforeSaving method, I think you can create an interface such as IDbContextBehavior with a method OnBeforeSaving and OnAfterSaving (and maybe OnModelCreating). You can create a class SoftDeleteBehavior that implements the interface. Then, you can create a DbContextWithBehavior class, and make your DbContext inherits from this class. Here's a pseudo code (not tested):

public abstract class DbContextWithBehavior : DbContext
{
    public IList<IBbContextBehavior> Behaviors { get; set; }

    public override async Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default)
    {
        foreach(var behavior in Behaviors) { behavior.OnBeforeSaving(); }
        await base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken)
        foreach(var behavior in Behaviors) { behavior.OnAfterSaving(); }
    }
}

public class MyDbContext : DbContextWithBehavior
{
    public MyDbContext()
    {
        Behaviors.Add(new SoftDeleteBehavior());
    }
}

Hope this helps!

Daniel Paiva -

I'll study it and maybe try it. I think it's a little beyond me but I'll try. Thank you so much

Thiago Sobral -

Awesome post
 thank you

Gavin Coates -

This looks like a great feature in theory, but it looks like you need to add this field and query filter for every table? If you could do it globally, that would be more use, but as it is, I'm not sure this is any easier than simply adding a field and setting it yourself through a Data Access Layer?

Meziantou -

You can apply the soft-delete behavior globally by applying the changes to all the entities of the model in OnModelCreating:

foreach(var entityType in modelBuilder.Model.GetEntityTypes())
{
    entityType.GetOrAddProperty("IsDeleted", typeof(bool));
}

Idem for OnBeforeSaving:

foreach (var entry in ChangeTracker.Entries())
{
    // ...
}
Dan Howell -

I think he meant how do you apply the select query filter to each entity type - this line:

modelBuilder.Entity<post>().HasQueryFilter(post => EF.Property<bool>(post, "IsDeleted") == false);

I am interested in this too - it doesn't appear that entityType has a HasQueryFilter method
 Any thoughts on this?

Meziantou -

Hi Dan,

I've added a new section in the post to answer your question: Automatically apply soft-delete to all entities of the model.

Hope this helps!

Leave a reply