To delete or not to delete, that is the question (pun intended).

The traditional way to remove information in a database is through a "hard delete." A hard delete permanently erases a record from the database table. While this seems straightforward, it presents a significant risk: once that data is gone, it's gone for good.

Instead of physically removing a record, a soft delete marks it as deleted, usually by setting a flag likeIsDeleted totrue. The record remains in the database, but it's effectively hidden from regular application queries.

Today, we'll dive into the details of how to implement soft deletes using EF Core. We'll discuss global query filters, explore efficient ways to handle soft-deleted data, and weigh the trade-offs.

What Is a Soft Delete?

A soft delete is a data persistence strategy that prevents the permanent deletion of records from your database. Instead of removing data from the database, a flag is set on the record, indicating it as "deleted."

This approach allows the application to ignore these records during normal queries. However, you can restore these records if necessary. Soft delete is also practical if you want to keep foreign key constraints in place. Soft delete is a "non-destructive" operation in contrast with hard delete, where data is completely removed from the database.

A hard delete uses the SQLDELETE statement:

DELETEFROMbookings.ReviewsWHEREId=@BookingId;

A soft delete, on the other hand, uses anUPDATE statement:

UPDATEbookings.ReviewsSETIsDeleted=1,DeletedOnUtc=@UtcNowWHEREId=@BookingId;

The data is still present in the database, and the operation can be undone.

But you need to remember to filter out soft-deleted data when querying the database:

SELECT*FROMbookings.ReviewsWHEREIsDeleted=0;

Let's see how we can implement soft delete withEF Core.

Soft Deletes Using EF Core Interceptors

EF Core interceptorsprovide a powerful mechanism for intercepting and modifying database operations. For example, you can intercept the saving changes operation to implement soft delete functionality.

Let's create anISoftDeletable marker interface to represent soft-deletable entities:

publicinterfaceISoftDeletable{boolIsDeleted{get;set;}DateTime?DeletedOnUtc{get;set;}}

The entities that should support soft delete will implement this interface. You will need to apply the respective database migration to create these columns.

The next component we need is aSaveChangesInterceptor, which allows us to hook into theSavingChangesAsync (orSavingChanges) method. We can access theChangeTracker and look for entries that implementISoftDeletable and are flagged for deletion. We can figure this out by checking if the entity state isEntityState.Deleted.

When we find the entities flagged for deletion, we loop through them and update their state toEntityState.Modified. You should also set the respective values for theIsDeleted andDeletedOnUtc properties. This will cause EF to generate anUPDATE operation instead of aDELETE operation.

publicsealedclassSoftDeleteInterceptor:SaveChangesInterceptor{publicoverrideValueTask<InterceptionResult<int>>SavingChangesAsync(DbContextEventDataeventData,InterceptionResult<int>result,CancellationTokencancellationToken=default){if(eventData.Contextisnull){returnbase.SavingChangesAsync(eventData,result,cancellationToken);}IEnumerable<EntityEntry<ISoftDeletable>>entries=eventData.Context.ChangeTracker.Entries<ISoftDeletable>().Where(e=>e.State==EntityState.Deleted);foreach(EntityEntry<ISoftDeletable>softDeletableinentries){softDeletable.State=EntityState.Modified;softDeletable.Entity.IsDeleted=true;softDeletable.Entity.DeletedOnUtc=DateTime.UtcNow;}returnbase.SavingChangesAsync(eventData,result,cancellationToken);}}

This approach ensures that all delete operations across the application respect the soft delete policy.

You'll need to register theSoftDeleteInterceptor with dependency injection and configure it with theApplicationDbContext.

services.AddSingleton<SoftDeleteInterceptor>();services.AddDbContext<ApplicationDbContext>((sp,options)=>options.UseSqlServer(connectionString).AddInterceptors(sp.GetRequiredService<SoftDeleteInterceptor>()));

If you want to learn more, here's an article with a few practical use cases forEF Core interceptors.

Automatically Filtering Soft-Deleted Data

To ensure that soft-deleted records are automatically excluded from queries, we can useEF Core global query filters. We can apply query filters to entities using theOnModelCreating method to automatically exclude records marked as deleted. This feature dramatically simplifies writing queries.

Here's how to configure the soft delete query filter:

publicsealedclassApplicationDbContext(DbContextOptions<UsersDbContext>options):DbContext(options){publicDbSet<Review>Reviews{get;set;}protectedoverridevoidOnModelCreating(ModelBuildermodelBuilder){modelBuilder.Entity<Review>().HasQueryFilter(r=>!r.IsDeleted);}}

A limitation is you can't have more than one query filter configured per entity.

However, it's sometimes useful to explicitly include soft-deleted records. You can achieve this using theIgnoreQueryFilters method.

dbContex.Reviews.IgnoreQueryFilters().Where(r=>r.ApartmentId==apartmentId).ToList();

Faster Queries Using Filtered Index

To enhance query performance, especially in tables with a significant number of soft-deleted records, you can usefiltered indexes. A filtered index only includes records that meet the specified criteria. This reduces the index size and improves query execution times for operations that exclude filtered records. Most popular databases support filtered indexes.

Here's how you can configure afiltered index with EF Core:

publicsealedclassApplicationDbContext(DbContextOptions<UsersDbContext>options):DbContext(options){publicDbSet<Review>Reviews{get;set;}protectedoverridevoidOnModelCreating(ModelBuildermodelBuilder){modelBuilder.Entity<Review>().HasQueryFilter(r=>!r.IsDeleted);modelBuilder.Entity<Review>().HasIndex(r=>r.IsDeleted).HasFilter("IsDeleted = 0");}}

TheHasFilter method accepts the SQL filter for records that will be included in the index.

You can also create a filtered index using SQL:

CREATEINDEXIX_Reviews_IsDeletedONbookings.Reviews(IsDeleted)WHEREIsDeleted=0;

You can learn more about filtered indexes from the documentation:

• SQL Server filtered index
• PostgreSQL partial index

Do You Really Need Soft Deletes?

It's worthwhile to think through if you even need to soft delete records.

In enterprise systems, you're typically not thinking about "deleting" data. There are business concepts that don't involve deleting data. A few examples are canceling an order, refunding a payment, or voiding an invoice. These "destructive" operations return the system to a previous state. But from a business perspective, you aren't really deleting data.

Soft deletes are helpful if there is a risk of accidental deletion. They allow you to easily restore soft-deleted records.

In any case, consider if soft deletes make sense from a business perspective.

Takeaway

Soft deletes offer a valuable safety net for data recovery and can enhance historical data tracking. However, it's crucial to assess whether they truly align with your application's specific requirements. Consider factors like the importance of deleted data recovery, any auditing needs, and your industry's regulations. Creating a filtered index can improve query performance on tables with soft-deleted records.

If you decide that soft deletes are a good fit, EF Core provides the tools necessary for a streamlined implementation.

Thanks for reading, and I'll see you next week!

P.S. Whenever you're ready, there are 3 ways I can help you:

1. Modular Monolith Architecture: This in-depth course will transform the way you build monolith systems. You will learn the best practices for applying the Modular Monolith architecture in a real-world scenario.Join the waitlist here.

2. Pragmatic Clean Architecture: This comprehensive course will teach you the system I use to ship production-ready applications using Clean Architecture. Learn how to apply the best practices of modern software architecture.Join 2,400+ students here.

3. Patreon Community: Think like a senior software engineer withaccess to the source code I use in my YouTube videos andexclusive discounts for my courses.Join 1,050+ engineers here.