Implementing Soft Delete in .NET with Entity Framework Core

Soft delete is a powerful technique for managing data in your applications. Instead of permanently removing records from your database, soft delete marks them as “deleted” while keeping the data intact. This approach offers several benefits, including easy data recovery and improved auditing capabilities. In this article, we’ll explore how to implement soft delete in a .NET application using Entity Framework Core.

Why Use Soft Delete?

Before diving into the implementation, let’s briefly discuss why soft delete is beneficial:

1. Data Recovery: Accidentally deleted data can be easily restored.
2. Auditing: Maintain a history of when items were “deleted”.
3. Consistency: Preserve referential integrity in related data.
4. Performance: In some cases, soft delete can be more performant than hard delete operations.

Implementation Steps

Let’s walk through the process of implementing soft delete in a .NET application using Entity Framework Core.

Step 1: Create the ISoftDeletable Interface

First, we’ll define an interface that our entities will implement to support soft delete:

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
    DateTime? DeletedAtUtc { get; set; }
}

This interface includes two properties:

• IsDeleted: A flag indicating whether the entity is considered deleted.
• DeletedAtUtc: The date and time when the entity was marked as deleted.

Step 2: Implement the Interface in Your Entity

Next, implement the ISoftDeletable interface in your entity classes. Here’s an example with a Movie entity:

public class Movie : ISoftDeletable
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int Year { get; set; }

    // ISoftDeletable implementation
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAtUtc { get; set; }
}

Step 3: Configure the DbContext with Global Query Filter

In your DbContext class, override the OnModelCreating method to add a global query filter for soft delete. This is a crucial step that eliminates the need to include the !IsDeleted condition in every query:

public class AppDbContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }

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

        // Apply global query filter for soft delete
        modelBuilder.Model.GetEntityTypes()
            .Where(entityType => typeof(ISoftDeletable).IsAssignableFrom(entityType.ClrType))
            .ToList()
            .ForEach(entityType =>
            {
                var parameter = Expression.Parameter(entityType.ClrType, "e");
                var property = Expression.Property(parameter, nameof(ISoftDeletable.IsDeleted));
                var falseConstant = Expression.Constant(false);
                var lambdaExpression = Expression.Lambda(Expression.Equal(property, falseConstant), parameter);

                modelBuilder.Entity(entityType.ClrType).HasQueryFilter(lambdaExpression);


                 // Add filtered index for better performance (Optional but Recommended)
                modelBuilder.Entity(entityType.ClrType)
                    .HasIndex(nameof(ISoftDeletable.IsDeleted))
                    .HasFilter($"\"{nameof(ISoftDeletable.IsDeleted)}\" = 0") // Use $"" for interpolated strings and escape column name for potential reserved words.
                    .HasDatabaseName($"IX_{entityType.ClrType.Name}_{nameof(ISoftDeletable.IsDeleted)}");

            });
    }
}

This enhanced OnModelCreating method now iterates through all entities in your model and applies the global query filter only to those that implement the ISoftDeletable interface. It also dynamically creates a filtered index for each of these entities, significantly improving query performance, especially for large datasets. The code also addresses potential issues with reserved keywords by escaping column names in the filter and index name.

Step 4: Implement Soft Delete in Your Service/Repository Layer

This example uses a more robust approach with ExecuteUpdateAsync for better performance, especially with large datasets:

public async Task<bool> SoftDeleteMovieAsync(int id)
{
    var affectedRows = await _context.Movies
        .Where(m => m.Id == id) // No need to check IsDeleted here due to the global filter
        .ExecuteUpdateAsync(s => s.SetProperty(m => m.IsDeleted, true)
                                   .SetProperty(m => m.DeletedAtUtc, DateTime.UtcNow));

    return affectedRows > 0; 
}


public async Task<Movie?> GetMovieByIdAsync(int id, bool includeDeleted = false)
{
    var query = _context.Movies.AsQueryable();

    if (includeDeleted)
    {
        query = query.IgnoreQueryFilters();
    }

    return await query.FirstOrDefaultAsync(m => m.Id == id);
}



// Example usage in a controller:
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteMovie(int id)
{
    var deleted = await _movieService.SoftDeleteMovieAsync(id);

    if (!deleted)
    {
        return NotFound();
    }

    return NoContent();
}

This service layer example clearly demonstrates the soft delete operation and how to retrieve data, both with and without applying the soft delete filter. The controller code provides a practical example of how this service method might be used.

Step 5: Querying Data (Including and Excluding Deleted Items)

With the global query filter in place, your normal queries will automatically exclude soft-deleted items:

// This query will only return non-deleted movies thanks to the global query filter.
public async Task<List<Movie>> GetAllMoviesAsync()
{
    return await _context.Movies.ToListAsync();
}

If you need to retrieve soft-deleted entities for specific purposes (e.g., an admin view), you can use the IgnoreQueryFilters() method:

// This query retrieves ALL movies, including soft-deleted ones.
public async Task<List<Movie>> GetAllMoviesIncludingDeletedAsync()
{
    return await _context.Movies.IgnoreQueryFilters().ToListAsync();
}


public async Task<List<Movie>> GetDeletedMoviesAsync()
{
    return await _context.Movies.IgnoreQueryFilters().Where(m => m.IsDeleted).ToListAsync();
}

These examples provide practical ways to retrieve both active and deleted records.

Conclusion

Implementing soft delete with a global query filter in Entity Framework Core provides a robust, efficient, and clean way to manage data deletion in your .NET applications. This method simplifies your data access layer, improves performance with filtered indexes, and provides greater flexibility in managing your data lifecycle. Remember to carefully consider the implications of soft delete on your application’s design and performance.