Introduction

Learn easy implementation for soft delete pattern using global query filters base on an Interface a column in database tables in a Windows Form project. 

An advantage for soft deletions over hard deletions is that those records marked as soft delete are readily available if needed. Example, a contact is temporally unavailable until a later date. By marking the contact, using a soft delete allows the contact to be undeleted and once again available. 

Entity Framework Core provides global filters which by using a LINQ predicate applied to entity types in a DbContext in OnModelCreating. There are also a method to ignore filters, which done in a local read, edit or hard delete. Using the contact example, when a contact becomes available again a user interface coupled with code or in SSMS can return the contact to available status.


Database preparation

  • For each table to implement soft delete add a bit field named Deleted.
  • For initial implementation use a update SQL statement to set each of the current rows Deleted = 0.

Delete Interface

Using an Interface provide easy detection which models implement soft deletes.
public interface ISoftDelete
{
    bool IsDeleted { get; set; }
}

DbContext modifications

In most example, for each table which implements soft delete will appear as shown below.

modelBuilder.Entity<Supplier>().HasQueryFilter(p => !p.IsDeleted);
modelBuilder.Entity<Shiper>().HasQueryFilter(p => !p.IsDeleted);

This works well for one or two tables although when a database with 20 or more tables were each table implements soft delete there is a better way to implement soft deletes.

Place the following method into the project's DbContext which assist with determining if a entity implements soft delete where the entity/model implements ISoftDelete.

public const string IsDeletedColumnName = "Deleted";
 
private LambdaExpression IsDeletedRestriction(Type type)
{
    var propMethod = typeof(EF).GetMethod(nameof(EF.Property),
        BindingFlags.Static |
        BindingFlags.Public)?.MakeGenericMethod(typeof(bool));
 
    var parameterExpression = Expression.Parameter(type, "it");
    var constantExpression = Expression.Constant(IsDeletedColumnName);
     
    var methodCallExpression = Expression.Call(
        propMethod ??
        throw new InvalidOperationException(), parameterExpression, constantExpression);
 
    var falseConst = Expression.Constant(false);
    var expressionCondition = Expression.MakeBinary(ExpressionType.Equal, methodCallExpression, falseConst);
 
    return Expression.Lambda(expressionCondition, parameterExpression);
}


At the end of OnModelCreating method a DbContext add the following code.

foreach (var mutableEntityType in modelBuilder.Model.GetEntityTypes())
{
    if (typeof(ISoftDelete).IsAssignableFrom(mutableEntityType.ClrType))
    {
        modelBuilder
            .Entity(mutableEntityType.ClrType)
            .HasQueryFilter(IsDeletedRestriction(mutableEntityType.ClrType));
    }
}

Performing a soft delete

A soft delete is performed no different than a conventional hard delete, set the state of the object to delete to EntityState.Deleted. In the example below data has been read (see the following for how data is read in) into a ComboBox, a BindingSource is the DataSource of the ComboBox so rather than touching the control the Current property of the BindingSource cast to the model type can then be marked and added to the context entities.

var currentSupplier = _supplierBindingSource.Current as Suppliers;
 
if (!Question($"Remove {currentSupplier.CompanyName}")) return;
 
using (var context = new NorthwindContext())
{
    context.Entry(currentSupplier).State = EntityState.Deleted;
    context.SaveChanges();
    _supplierBindingSource.RemoveCurrent();
}

Accessing both soft deletes and all records

Since a filter has been set on a model no records matching the filter condition will be accessible unless IgnoreQueryFilters is applied. In the following button click
  • First lambda statement obtains a count of records not soft deleted.
  • The second lambda statement uses a predicate in tangent with IgnoreQueryFilters to get a count of soft deleted records.
  • Last lambda uses IgnoreQueryFilters to get a total count of records regardless of the Delete column values.
private void FilteredCountButton_Click(object sender, EventArgs e)
{
    var sb = new StringBuilder();
 
    using (var context = new NorthwindContext())
    {
        // active records
        var count = context.Suppliers.AsNoTracking().Count();
        sb.AppendLine($"Not soft deleted: {count}");
 
        // marked as soft delete
        count = context.Suppliers.IgnoreQueryFilters().AsNoTracking()
            .Count(s => s.Deleted == true);
 
        sb.AppendLine($"soft deleted: {count}");
 
        // total count of records
        count = context.Suppliers.IgnoreQueryFilters().AsNoTracking().Count();
        sb.AppendLine($"Total record count: {count}");
    }
 
    MessageBox.Show(sb.ToString());
}

Saving soft deletes

Saving changes, override SaveChanges (SaveChangesAsync) to get from the ChangeTracker items which are marked to delete than implement ISoftDelete. Of these items set the IsDelete property (marked in the model as not mapped) which sets the physical column to true and finally change the item state to Modified to prevent a hard delete. If both SaveChanges and SaveChangesAsync are both overridden all code other than the return statement may be placed into a private void method to be executed in both SaveChanges and SaveChangesAsync.

public override int SaveChanges()
{
    var deletedEntityEntries = ChangeTracker
        .Entries()
        .Where(item =>
            item.Entity is ISoftDelete &&
            item.State == EntityState.Deleted).ToList();
 
    foreach (var itemEntityEntry in deletedEntityEntries)
    {
        ((ISoftDelete)itemEntityEntry.Entity).IsDeleted = true;
        itemEntityEntry.State = EntityState.Modified;
    }
 
    return base.SaveChanges();
}

Summary

Code presented to provide a consistent and easy method to setup soft deletes with minimal code for all tables to implement soft deletes. 

See also

Setting up for running code

Source code


The following GitHub repository contains source code and SQL-Server script to create and populate tables.

External tools

EF Core Power Tools by ErikEJ

Useful design-time DbContext features, added to the Visual Studio Solution Explorer context menu.

When right-clicking on a C# project, the following context menu functions are available:

Reverse Engineer - Generates POCO classes, derived DbContext and Code First mapping for an existing SQL Server, SQLite, Postgres, MySQL or SQL Server Compact database, a SQL Server Database project or a .dacpac file.

When creating models with an existing database this tool is a must have.