Global Query Filters for Soft Delete, Tenant Scope, Active Rows, and Security-Sensitive Predicates

Learning outcomes

1. The problem: one database, many organizations, one dangerous omission

ServiceHub is about to host several customer organizations in the same database. A normal WorkOrders.Where(...) query is now security-sensitive: forgetting one tenant predicate can return another organization’s data. At the same time, operations wants “deleted” work orders retained for audit, while the normal UI should hide them. Repeating w => w.TenantId == tenantId && !w.IsDeleted in every repository call is not a durable control because omission is the failure mode.

A global query filter is a LINQ predicate attached to an EF entity type in the model. When that root entity type is queried, EF composes the filter into the expression tree before translation. This is valuable default scoping, but it lives in the application model and can be disabled. Treat it as one defense layer, not as the database security boundary.

2. Make tenancy and lifecycle visible in the relational schema

Chapter 22 deliberately evolves the existing work_orders row. The migration adds a tenant discriminator, a soft-delete flag, and an active flag. Existing rows must be backfilled deterministically before making tenant_id required. This is a schema/data migration, not a hidden model tweak.

public sealed partial class WorkOrder{    public string TenantId { get; private set; } = null!;    public bool IsDeleted { get; private set; }    public bool IsActive { get; private set; } = true;    public void SoftDelete()    {        IsDeleted = true;        AdvanceRevision();       // Chapter 04 application-managed token    }}

migrationBuilder.AddColumn<string>(    name: "tenant_id", table: "work_orders", nullable: false,    defaultValue: "legacy");migrationBuilder.AddColumn<bool>(    name: "is_deleted", table: "work_orders", nullable: false,    defaultValue: false);migrationBuilder.AddColumn<bool>(    name: "is_active", table: "work_orders", nullable: false,    defaultValue: true);migrationBuilder.CreateIndex(    name: "ix_work_orders_tenant_active",    table: "work_orders",    columns: new[] { "tenant_id", "is_deleted", "is_active" });

The default legacy value is a migration bridge for the disposable course database, not a production tenant assignment policy. A real deployment should backfill from authoritative ownership data, verify counts, then remove any unsafe default that could silently assign future rows to the wrong tenant.

3. Context instance state becomes a query parameter

For discriminator-column tenancy, the tenant ID belongs on the short-lived context instance. It does not change the model’s shape, so it should not become part of the model-cache key. EF can parameterize a filter that references context instance state.

public sealed class ServiceHubContext : DbContext{    public string TenantId { get; }    public ServiceHubContext(        DbContextOptions<ServiceHubContext> options,        ITenantContext tenant) : base(options)    {        TenantId = tenant.RequiredTenantId;    }    protected override void OnModelCreating(ModelBuilder modelBuilder)    {        base.OnModelCreating(modelBuilder);        modelBuilder.Entity<WorkOrder>()            .HasQueryFilter(w =>                w.TenantId == TenantId &&                !w.IsDeleted &&                w.IsActive);    }}

Lesson 2 replaces this combined predicate with named EF Core 10 filters. Here the single predicate makes the mechanics easy to observe first.

4. Observe the SQL: defaults are still SQL predicates

await using var db = factory.CreateForTenant("tenant-a");var query = db.WorkOrders    .Where(w => w.Priority >= 2)    .OrderBy(w => w.Id)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary });Console.WriteLine(query.ToQueryString());var rows = await query.ToListAsync(ct);

SELECT "w"."work_order_id", "w"."work_order_number", "w"."summary"FROM "work_orders" AS "w"WHERE "w"."tenant_id" = @__ef_filter__TenantId_0  AND NOT ("w"."is_deleted")  AND "w"."is_active"  AND "w"."priority" >= 2ORDER BY "w"."work_order_id"

The key evidence is the parameterized tenant predicate. ToQueryString() proves translation shape, not authorization correctness or runtime plan quality. Command logs should confirm the same predicate on the executed command while sensitive-data logging remains disabled.

5. Deliberate failure: IgnoreQueryFilters is a real bypass

A support engineer needs to restore a soft-deleted work order and discovers IgnoreQueryFilters(). The dangerous shortcut is to use it in a normal tenant endpoint and then add only a work-order number predicate.

var row = await db.WorkOrders    .IgnoreQueryFilters()    .SingleAsync(w => w.WorkOrderNumber == request.Number, ct);

SELECT ...FROM "work_orders" AS "w"WHERE "w"."work_order_number" = @__request_Number_0LIMIT 2

The absence of tenant_id is the bug. The safe repair is an explicitly authorized administrative path that disables only the lifecycle filter where EF Core 10 named filters are available, while preserving the tenant filter. Lesson 2 implements that pattern.

6. Soft delete is a write policy, not just a read filter

If callers continue to call Remove, an application-level write hook can convert a tracked delete into an update. It must also preserve the Chapter 04 concurrency token so a stale request does not silently delete a newer version.

private void ApplySoftDeletes(){    ChangeTracker.DetectChanges();    foreach (var entry in ChangeTracker.Entries<WorkOrder>()                 .Where(e => e.State == EntityState.Deleted))    {        entry.State = EntityState.Modified;        entry.Entity.SoftDelete();        entry.Property(w => w.IsDeleted).IsModified = true;        entry.Property(w => w.Revision).IsModified = true;    }}

UPDATE "work_orders"SET "is_deleted" = 1, "revision" = @p0WHERE "work_order_id" = @p1  AND "revision" = @p2RETURNING 1;

ExecuteDelete is different: it is immediate set-based DML and does not run this tracked-state conversion. A production soft-delete policy must decide whether such APIs are forbidden, wrapped, or explicitly implemented as ExecuteUpdate.

7. Required navigations can change cardinality under filters

Suppose a required WorkOrder.TenantProfile navigation points to a principal that itself has an active-tenant filter. EF is allowed to use an inner join for a required navigation. If the principal is filtered out, the work order may disappear too. That is not “filter leakage”; it follows the relational meaning of requiredness plus an inner join.

Always inspect generated SQL for filter-sensitive Includes/joins. Changing IsRequired(false) merely to “fix” a query is a modeling decision with constraint consequences, not a query-tuning trick.

8. Reproducible SQLite lab

1. Create a disposable servicehub-tenancy-lab.db from the Chapter 21 schema, then apply AddTenantLifecycleColumns.
2. Seed two tenants, three visible work orders each, one soft-deleted row, and one inactive row.
3. Open separate contexts for tenant-a and tenant-b; assert the same unqualified LINQ query returns disjoint IDs.
4. Capture ToQueryString() and command logs with sensitive data disabled; verify the tenant predicate is parameterized.
5. Run the deliberate IgnoreQueryFilters() query and record why it crosses the data boundary.
6. Soft-delete one tracked row and verify the database receives an UPDATE with the original Revision, not a physical DELETE.
7. Reset by deleting only the disposable lab file.

9. Production judgment

Global filters are appropriate for default lifecycle and tenant scoping when their bypass paths are tightly controlled and verified. They do not replace authentication, authorization, database constraints, least-privilege credentials, row-level security where available, or adversarial tests. Keep the context short-lived, derive tenant identity from trusted server-side state, test every explicit bypass, and include the tenant discriminator in indexes that support real query shapes.

Lesson 2 uses EF Core 10 named filters so a privileged workflow can reveal soft-deleted rows without dropping tenant isolation at the same time.

Authoritative references

• Global query filters — soft delete, multi-tenancy, named filters, bypass, and required-navigation caveats
• What's new in EF Core 10 — named query filters and EF Core 10 feature status
• EF Core multi-tenancy — supported discriminator/database-per-tenant patterns and schema-per-tenant warning
• EF Core DbContext configuration — short-lived context and configuration fundamentals
• Foreign and principal keys — database relationship enforcement needed for tenant-aware keys later in the chapter
• SQLite foreign keys — SQLite database-side referential enforcement for the free local lab