EF Core Global Query Filters for Soft Delete

If every LINQ query must remember !IsDeleted && ClinicId == current, someone will forget. A nightly CSV then emails clinic B clinic A’s encounters. I have cleaned that up. Global query filters make the common case automatic. They do not replace authorization.

This URL is how I wire HasQueryFilter and how I bypass it without a leak. The interview prompt (“the job ignored the filter”) is EF Core interview questions. Who deleted the row belongs in an audit interceptor, not in this filter.

The filter I put on healthcare rows

public class AppDbContext : DbContext
{
    private readonly Guid _clinicId;

    public AppDbContext(DbContextOptions<AppDbContext> options, ITenantContext tenant)
        : base(options)
    {
        _clinicId = tenant.ClinicId;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Encounter>().HasQueryFilter(e =>
            !e.IsDeleted && e.ClinicId == _clinicId);
    }
}

db.Encounters.ToListAsync() becomes WHERE IsDeleted = 0 AND ClinicId = @clinicId. Soft delete is an UPDATE to IsDeleted, not ExecuteDelete, unless the row is allowed to disappear.

I inject tenant from the request scope, not a static Guid. A singleton DbContext with the first clinic that booted is how the wrong tenant’s numbers show up in a cache. That captive-dependency story is in the interview post and in DI lifetimes.

Bypass without leaking the other clinic

Admin restore and compliance exports need deleted rows. IgnoreQueryFilters() turns both the soft-delete and the tenant predicate off.

var includingDeleted = await db.Encounters
    .IgnoreQueryFilters()
    .Where(e => e.ClinicId == clinicId)
    .ToListAsync(ct);

If you skip the extra Where on ClinicId, you just queried every clinic. I treat IgnoreQueryFilters in a PR as a review event. Re-apply the tenant predicate in the same statement. Log the clinic id from the job payload, not from “whatever the context happened to hold.”

Includes follow the ignore

IgnoreQueryFilters() applies to the entire query, including Included collections. Restore an encounter and you also get soft-deleted lines unless you filter lines yourself. If you only needed deleted headers, do not Include children on that query.

Raw SQL / FromSqlRaw never saw the filter. Neither did a second DbContext constructed in a hosted service with ClinicId = Guid.Empty. Those are the other leak paths I list in interviews.

Filters are not a substitute for policies

A user in clinic A can still hit GET /encounters/{id} for an id from clinic B if you only “hide” rows in LINQ and never check the resource. I still load with the filter and enforce a policy. The filter stops accidental lists. It does not stop a guessed GUID if you query by id with IgnoreQueryFilters in a debug helper you forgot to delete.

Do not log encounter payloads when you debug a filter miss. PII redaction is the logging article.

If a SaaS job exported the wrong tenant’s rows, contact me. The HasQueryFilter line plus whether the job called IgnoreQueryFilters is the whole incident review.