EF Core global query filters as a tenant fence, and where they leak

The first leak I found was in an export. A customer downloaded a CSV of their invoices and got 31 rows more than they had invoices. The extra rows belonged to a tenant three ids further down the catalog, and they had been in every export that customer had run for five weeks. The cause was not a missing WHERE, exactly. The cause was a developer who had replaced a slow repository call with Database.SqlQuery against a view, and the view did not carry the tenant column.

That was April 2013 and Entity Framework 6, which had no notion of a global filter at all. The fence was a Where clause copied into every repository method by hand, forty or fifty times, and the one query that did not go through a repository was the one that leaked. When global query filters arrived in EF Core 2.0 four years later I rewrote this sheet around them, gratefully, and then spent the next few years finding out what they do not cover. They are the best default a shared-row database can have, because they turn "remember to filter" into "remember to opt out". They are a default, not a boundary. The filter lives in one place in the query pipeline, and everything that goes around that place goes around the fence. This sheet draws where it lives, the three doors around it, and the second fence I now consider mandatory.

Where the filter lives

A global query filter is a LINQ predicate attached to an entity type in the model. When the query pipeline expands a query over that type, it adds the predicate as if you had written a Where yourself. Because the predicate can reference the DbContext instance, the tenant id is read at query time from the context, which means the context needs to know its tenant when it is constructed. That is what the scoped ITenantContext from sheet T-02 is for.

public sealed class AppDbContext(DbContextOptions<AppDbContext> options, ITenantContext tenant)
    : DbContext(options)
{
    private readonly Guid _tenantId = tenant.Tenant.Id;

    protected override void OnModelCreating(ModelBuilder b)
    {
        foreach (var type in b.Model.GetEntityTypes()
                     .Where(t => t.BaseType is null && typeof(ITenantOwned).IsAssignableFrom(t.ClrType)))
        {
            typeof(AppDbContext)
                .GetMethod(nameof(ApplyTenantFilter), BindingFlags.NonPublic | BindingFlags.Instance)!
                .MakeGenericMethod(type.ClrType)
                .Invoke(this, [b]);
        }

        b.Entity<Invoice>().HasQueryFilter("SoftDelete", i => !i.IsDeleted);
    }

    private void ApplyTenantFilter<T>(ModelBuilder b) where T : class, ITenantOwned
        => b.Entity<T>().HasQueryFilter("Tenant", e => e.TenantId == _tenantId);
}

Two things in that snippet are deliberate. The loop applies the filter to every entity that implements ITenantOwned, so adding an entity to the model without a filter requires forgetting the interface, which the compiler will not let you do if your repositories are constrained on it. And the filters are named, which arrived in EF Core 10. Until then you had one lambda per entity with the conditions joined by &&, and an admin screen that needed to see soft-deleted rows had to call IgnoreQueryFilters() and lose the tenant filter with it. Now it calls IgnoreQueryFilters(["SoftDelete"]) and keeps the fence.

The three doors

The first door is IgnoreQueryFilters(). It exists because admin tooling, reporting and migration scripts genuinely need to see across tenants, and every product grows one of those within a year. The problem is not the method; it is that a call written for the admin API gets copied into a customer-facing handler during a refactor. I grep for it in code review, and since EF Core 10 I reject any call that does not name the filters it is ignoring. An unnamed IgnoreQueryFilters() now needs a comment and a second reviewer.

The second door is SQL that is not a LINQ query over an entity. FromSql on a DbSet is fine: EF wraps your SQL in a subquery and composes the filter on top, so the tenant predicate is still there. The leak in 2013 was a view without the column, not the method, and that method has since become the safe one. But SqlQuery<T> for unmapped types, ExecuteSql, a stored procedure, Dapper, or a SqlCommand you opened yourself all produce SQL that EF never sees. Every one of those is a query with no fence, and every one of them will be written at some point because the LINQ was slow.

The third door is an entity you forgot. Give Invoice a filter and InvoiceLine none, and context.InvoiceLines.Where(l => l.Amount > 1000) returns lines from every tenant, cheerfully. The convention loop above closes this for new entities, but there is a subtler version. When a required navigation points at a filtered type, EF generates an inner join, and a parent whose child is filtered out disappears from the result. That is a loss rather than a leak, but a report that silently drops a third of its rows is its own kind of incident. The fix is consistent filters on both ends of every relationship, which the loop also gives you.

Bulk operations and the write side

ExecuteUpdate and ExecuteDelete, which arrived in EF Core 7, go through the query pipeline, so the tenant filter is applied to the generated UPDATE or DELETE. That surprised me pleasantly when I checked. What they skip is SaveChanges, and with it every interceptor and override you have hung on it. If your tenant id is stamped on new rows in a SaveChanges override, a bulk update cannot break that, since it cannot insert; but the combination IgnoreQueryFilters().ExecuteDeleteAsync() deletes across every tenant in one round trip, with no change tracker to argue with. I treat that pairing as a build error and have an analyzer that flags it.

The write side has its own gap that no query filter covers. A filter constrains what you read. It says nothing about Add, so a handler that constructs an Invoice with a TenantId copied from a request body will insert it into the wrong tenant, and the filter will then hide the evidence from the tenant that wrote it. The fence for that is a SaveChangesInterceptor, which sees every tracked entity before it hits the database.

public sealed class TenantWriteFence(ITenantContext tenant) : SaveChangesInterceptor
{
    // The synchronous SavingChanges override mirrors this one and calls Guard too.
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result, CancellationToken ct = default)
    {
        Guard(eventData.Context!);
        return ValueTask.FromResult(result);
    }

    private void Guard(DbContext db)
    {
        foreach (var entry in db.ChangeTracker.Entries<ITenantOwned>())
        {
            if (entry.State == EntityState.Added)
                entry.Entity.TenantId = tenant.Tenant.Id;
            else if (entry.State is EntityState.Modified or EntityState.Deleted
                     && entry.Entity.TenantId != tenant.Tenant.Id)
                throw new CrossTenantWriteException(entry.Metadata.ClrType.Name);
        }
    }
}

The interceptor overwrites the tenant id on inserts rather than validating it, on the grounds that a handler has no legitimate reason to set it. On updates and deletes it throws, and that exception has fired exactly twice in production, both times during a data migration, both times correctly.

The second fence lives in the database

Everything above is application code, and application code can be bypassed by the next developer with a SqlConnection. The fence I now consider mandatory for shared rows is row-level security in Azure SQL, with a filter predicate on TenantId that reads the tenant from SESSION_CONTEXT. The application sets the session context when it opens a connection, through an IDbConnectionInterceptor that runs sp_set_session_context with the tenant id and @read_only = 1, and from that point the database itself refuses to return rows for any other tenant. It does not matter whether the SQL came from LINQ, from SqlQuery, from Dapper or from a stored procedure somebody wrote in 2013 and nobody has opened since.

The costs are real but modest. The predicate function adds a join hint to every plan, which on a well-indexed table with TenantId leading the clustered key is nearly free. Admin and migration connections need a separate database user that the policy exempts, and that user must never be the one the web app uses. And the session context must be set on every connection, including the ones a pool hands back, which is why it goes in the connection interceptor rather than in the DbContext constructor. Connection reset clears it on return to the pool, so a reused connection arrives clean and the interceptor sets it again.

What I run today

Named query filters applied by convention to every ITenantOwned entity, a SaveChangesInterceptor that stamps inserts and rejects cross-tenant writes, an analyzer that fails the build on unnamed IgnoreQueryFilters() and on any bulk operation composed on it, and row-level security on SESSION_CONTEXT as the fence that holds when all of that is bypassed. The query filter is still the piece that makes the ordinary day safe. The database policy is the piece that makes the extraordinary day survivable, and I would not ship shared rows without it again.

Drawn from

• Global query filters in EF Corelearn.microsoft.com
• SQL queries in EF Corelearn.microsoft.com
• ExecuteUpdate and ExecuteDeletelearn.microsoft.com
• Row-level security in SQL Server and Azure SQLlearn.microsoft.com