Implementing Soft Delete In EF Core With Global Query Filters

Deleting a record from a production database is often a bigger decision than the word “delete” suggests. A customer may need a record restored, an administrator may need an audit trail, and a support team may need to understand what happened months after the original request. A soft delete keeps the row while marking it as inactive, allowing the application to hide it from ordinary queries.

Entity Framework Core makes this approach practical through global query filters. A filter can automatically exclude deleted entities from LINQ queries, so developers do not need to remember an IsDeleted condition in every repository method. The pattern works well for web applications, Azure-hosted services, internal systems, and multi-tenant platforms.

The implementation still requires careful design. Deletion commands, relationships, indexes, reporting, background jobs, privacy obligations, and administrative access all need to agree on what “deleted” means. For Australian teams, data residency in Azure Australia regions and the requirements of the Privacy Act may also influence how deleted information is retained and eventually purged.

Why Soft Delete Fits Modern Applications

A hard delete removes a database row permanently, usually through SQL DELETE. That can be appropriate for temporary data or information subject to a strict destruction policy, but it makes recovery and investigation difficult. Soft deletion replaces physical removal with a state change, commonly represented by an IsDeleted Boolean and a deletion timestamp.

The pattern is especially useful when records have business history. An order, project, subscription, or support ticket may be referenced by other entities and included in audit reports. Marking it as deleted preserves those relationships while keeping it out of normal customer-facing screens.

A typical entity might include the following properties:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;

    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAtUtc { get; set; }
    public string? DeletedBy { get; set; }
}

Using UTC timestamps is important when applications operate across Sydney, Perth, Melbourne, and overseas locations. DateTimeOffset also retains the offset when values are exchanged with external systems, although storing a consistent UTC value remains the usual database convention.

Design The Deletion State Carefully

A Boolean is enough for a simple application, but larger systems often benefit from a small deletion model. DeletedAtUtc records when the action occurred, while DeletedBy identifies the user, service, or job responsible. A reason field can help support staff distinguish an accidental removal from a compliance request.

The deletion fields should be treated as part of the domain rather than as arbitrary flags scattered throughout controllers. An interface provides a consistent contract:

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
    DateTimeOffset? DeletedAtUtc { get; set; }
    string? DeletedBy { get; set; }
}

Entities that implement this interface can share conventions in the DbContext. This reduces repetition and makes it easier to apply the same behaviour to customers, documents, comments, and other aggregate members.

A soft delete should generally be idempotent. If a request is submitted twice, the second request should not produce a different result or reset the original deletion timestamp. Restore operations should be explicit, authorised, and recorded. In a business application, “undelete” is a meaningful state transition rather than a casual update to a Boolean.

Configure Global Query Filters

The global filter belongs in OnModelCreating. For a single entity, the configuration is straightforward:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasQueryFilter(product => !product.IsDeleted);
}

Every normal LINQ query for Product now adds the equivalent of WHERE IsDeleted = 0. This includes queries created through common repository methods and navigation loading that uses the entity type. The calling code can focus on active products without repeating the predicate.

For several soft-deletable entities, each type needs a filter. A reusable convention can apply filters through model metadata, but dynamically constructing expression trees requires care. A simpler explicit configuration is often easier to maintain for a modest model. Teams should avoid hiding too much magic inside reflection if developers will need to debug generated SQL during an incident.

Administrators and maintenance jobs can intentionally bypass the filter:

var deletedProducts = await db.Products
    .IgnoreQueryFilters()
    .Where(product => product.IsDeleted)
    .ToListAsync();

This capability must be restricted to trusted application paths. A controller that exposes IgnoreQueryFilters() without authorisation can unintentionally reveal customer data that users believe has been removed. For technical discussions, implementation examples and related sessions are available through the conference programme, alongside broader .NET and cloud topics.

Keep Writes, Relationships, And Restores Predictable

A query filter changes reads; it does not automatically change the behaviour of Remove. If application code calls db.Products.Remove(product), EF Core will normally issue a physical DELETE. The service must instead set the deletion properties, or the DbContext can override SaveChanges and convert deleted entries into modified entries.

A basic override looks like this:

public override int SaveChanges()
{
    ConvertDeletesToSoftDeletes();
    return base.SaveChanges();
}

private void ConvertDeletesToSoftDeletes()
{
    foreach (var entry in ChangeTracker.Entries<ISoftDeletable>())
    {
        if (entry.State == EntityState.Deleted)
        {
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAtUtc = DateTimeOffset.UtcNow;
        }
    }
}

An asynchronous override should apply the same rule to SaveChangesAsync. In a real service, the current user identity can be supplied through an injected context accessor rather than leaving DeletedBy empty. A SaveChangesInterceptor is another suitable option when the team wants the behaviour separated from a particular context class.

Relationships need separate attention. A soft-deleted parent may still have active children, while a filtered navigation property can make those children appear to have no parent. Required foreign keys and cascade delete rules may also produce surprising results if EF Core attempts to physically delete related records. Define whether child entities are deleted with the parent, retained independently, or marked through a domain operation.

Restoring an aggregate deserves the same care. Restoring a parent while related children remain deleted can create an incomplete screen or invalid workflow. A restore service should load the required graph with IgnoreQueryFilters(), check authorisation and concurrency, then restore only the entities allowed by the business rules.

Test The Queries And Data Lifecycle

A global filter is easy to demonstrate with one unit test, but production confidence requires tests across the entire data lifecycle. Create an active row, mark it deleted, verify that ordinary queries exclude it, and then use an administrative query to confirm that it still exists. Test restoration separately rather than treating it as the reverse of deletion by assumption.

Generated SQL should be inspected for important queries. A filter may be present in SQL while a custom projection, raw command, view, or reporting process follows a different path. IgnoreQueryFilters() is useful for controlled administrative reads, but raw SQL executed through ExecuteSqlRaw does not gain protection from a LINQ filter. Database scripts and scheduled jobs therefore need their own review.

Concurrency matters when two administrators act on the same record. Add a row-version column or another concurrency token where deletion and restoration can race with edits. An operation that restores stale data should fail clearly instead of silently overwriting a newer deletion decision.

The retention policy must be documented separately from the read filter. Soft delete is not the same as permanent erasure. Australian organisations may need to consider the Privacy Act, contractual retention terms, industry rules, and a customer’s request for personal information to be removed. A scheduled purge can permanently delete records after the approved retention period, with appropriate backups and audit controls.

Avoid Hidden Data Access Traps

Global filters are convenient because they operate implicitly, but implicit behaviour can confuse developers working on reports, exports, and background processing. A nightly job might count only active records when finance expects a historical total. A support screen might show an empty result because the operator forgot that deleted rows are filtered.

Document which services are allowed to see inactive data. Separate application methods such as GetActiveProductsAsync and GetDeletedProductsForAdminAsync are clearer than allowing every caller to append IgnoreQueryFilters(). The naming makes intent visible during code review and reduces accidental exposure.

Be especially careful with navigation properties and projections. If an order points to a soft-deleted customer, a query may return the order while the customer navigation is absent or filtered. That behaviour can be correct for a user-facing page but unsuitable for an audit export. Explicitly choose whether a query needs active data only, historical data, or a mixture of both.

Database indexes should support the filtered access pattern. An index beginning with IsDeleted, tenant ID, status, or a frequently searched business key may reduce the cost of active-record queries. On large SQL Server installations, filtered indexes can be useful, subject to the database provider and the query shapes generated by EF Core.

Operational Checks For Production

A reliable soft-delete implementation depends on conventions that survive staff changes and urgent fixes. This is particularly relevant for Australian teams supporting customers across AEST, ACST, and AWST, where an overnight purge or incident response may cross several reporting periods.

Before releasing the feature

• Confirm every delete path uses the domain service or interception rule.
• Review foreign keys, cascade behaviour, and filtered navigation properties.
• Add tests for active reads, deleted reads, restoration, and concurrency.
• Decide which data must eventually be permanently purged.

During ongoing operations

• Monitor queries that use IgnoreQueryFilters() and restrict them by role.
• Log the actor, timestamp, reason, and correlation ID for deletion events.
• Check indexes and query plans as the archive grows.
• Verify backups, replicas, exports, and search indexes follow the retention policy.

Teams hosting workloads in Azure Australia East or Australia Southeast should also check where database backups, logs, and replicas are stored. Data residency can affect the overall privacy design even when the application itself behaves correctly. For a business operating around Sydney or Melbourne, an application may appear responsive and compliant while a third-party export quietly copies deleted records to another region.

Soft delete works best when it is treated as a complete data lifecycle rather than a single EF Core configuration line. Global query filters provide a strong default for ordinary reads, while explicit commands, authorisation, auditing, relationship rules, testing, and retention controls determine whether the pattern remains safe at scale.