Branch-free tenant filters in EF Core and SQL Server

SpendCentric stores personal data and organization data in the same tables. Every query on a tenant-scoped entity passes through a global query filter, and that filter now produces a single tenant predicate for the mode of the request. A personal request sends org IS NULL AND owner = @owner to the database, and an organization request sends org = @org. Each statement can seek the index built for it.

The platform is built on .NET with EF Core on SQL Server. The mode check stays in the filter definition, but EF Core evaluates it before writing the SQL, through EF.Constant, so the branch that cannot match never reaches the database. The pattern covers all 14 tenant-scoped entity sets, is paired with indexes keyed on the sort order of the busiest lists, and is pinned by a unit test that inspects the generated SQL.

The sections below cover why one filter for both modes prevented index seeks, how EF Core 10 turns EF.Constant inside a query filter into a literal and removes the dead branch, which caches see the mode, the indexes behind each branch, and the test that keeps the SQL in shape.

Two tenancy modes in one table

A row belongs either to one person or to an organization. Personal rows have no organization and are owned by a single user. Organization rows carry the organization and are visible to every member; the owner column is kept on them for attribution. The active organization is resolved for each request, and a request without one runs in personal mode.

Mode	A row is visible when
Personal	The organization column is NULL and the owner column is the current user
Organization	The organization column is the active organization

The column names in this post are generic: org for the organization, owner for the user, and a date column for the transaction date.

One filter, both branches

A global query filter is attached to an entity type in the model and added to every query on that type. The tenant filter expressed both modes as one predicate:

filter(row) =
       (no active organization AND row.org IS NULL AND row.owner = current user)
    OR (active organization    AND row.org = active organization)

When a query filter reads a member of the context, EF Core does not bake the value into the model. It reads the value from the current context on every execution and sends it as a parameter whose name starts with ef_filter. The two mode checks became two boolean parameters, and both branches reached SQL Server. The shape of the statement, with parameter names simplified:

WHERE ((@personal = CAST(1 AS bit) AND e.org IS NULL AND e.owner = @owner)
    OR (@organization = CAST(1 AS bit) AND e.org = @org))
  AND e.is_deleted = CAST(0 AS bit)

SQL Server compiles one plan per statement text and reuses it for any parameter values, so the plan has to return the right rows whichever flag is set. Each branch matches a different index, and a seek on either index alone would miss the rows of the other branch. For the dashboard’s list of latest transactions and the paged transaction list, the optimizer chose a full clustered index scan: every tenant’s rows were read to return one tenant’s page.

EF.Constant moves the branch out of the SQL

The context exposes a personal-mode flag, true when no organization is active. The filter reads that flag through EF.Constant instead of comparing the organization with NULL. The rows each mode can see are unchanged:

personal_mode = (active organization is none)          # read from the context per query

filter(row) =
       (EF.Constant(personal_mode)     AND row.org IS NULL AND row.owner = current user)
    OR (NOT EF.Constant(personal_mode) AND row.org = active organization)

EF.Constant was introduced in EF Core 8.0.2 and tells EF Core to put a value into the SQL as a literal instead of a parameter. SpendCentric targets EF Core 10. In that version, a flag read through EF.Constant inside a query filter goes through four stages:

Stage	Runs	What happens to the flag
Query compilation	Once per query shape	The filter is expanded into the query. The flag is a context member, so it becomes a filter parameter whose value is read from the current context, marked to be inlined rather than sent.
Compiled-query cache	Every execution	The key is the LINQ expression and a few options. The flag is not part of it, so both modes share one compiled query.
SQL generation	Every execution	The flag’s value is written into the SQL tree as a literal. true AND x folds to x, false AND x to false, and x OR false to x, which removes the dead branch.
SQL Server plan cache	Every execution	The statement text differs by mode, so SQL Server compiles and caches one plan per mode, each with a single tenant predicate.

The owner and the organization stay parameters. Only the flag, which has two possible values, becomes a literal, so each query has at most two statement texts. The resulting predicates:

-- personal request
WHERE e.org IS NULL AND e.owner = @owner AND e.is_deleted = CAST(0 AS bit)

-- organization request
WHERE e.org = @org AND e.is_deleted = CAST(0 AS bit)

Where the mode enters the caches

The flag is read on every execution, not when the context is created. The active organization is set by request middleware, which can run after the context has been constructed; a query issued after that point produces the organization SQL. Because the flag never enters the compiled-query cache key, the LINQ-to-SQL translation still happens once per query shape.

The cost sits in the last stage. Once a literal derived from a parameter value is in the SQL tree, EF Core marks the generated command as not cacheable. The final processing of the SQL tree and the writing of the statement text run on every execution of a filtered query, instead of being served from EF Core’s command cache. This cost was not measured in isolation.

Fourteen entity sets

Every filter with the personal and organization shape uses the same pattern. Entities that exist only in personal scope keep a single owner predicate.

Filter shape	Sets	Example
Tenant columns on the row	8	Accounts, transactions and the ledger tables
Tenant columns on a parent row, reached through a navigation	5	A split line filtered through its transaction
Owner always matched, organization column chosen by mode	1	Saved list filters

For filters that reach the tenant columns through a navigation, the join stays and the pruned predicate is applied to the parent. The rule is the same in every shape: the branch is chosen by a literal, and the identifiers are parameters.

Indexes that match each branch

A pruned predicate only helps if an index leads with its tenant column. The two hot lists order by transaction date, then last update, then primary key, and read the first page.

Index	Key	Filter	Include
Personal lists	owner, date, updated	not deleted	org
Organization lists	org, date, updated	org IS NOT NULL, not deleted	owner

The key follows the sort order, so the first page is the first rows of one range, read without a sort. The primary key completes the order, because SQL Server adds the clustered key to every nonclustered index. Including the organization column in the personal index lets the seek test org IS NULL without a lookup into the table. The organization index holds only organization rows, so personal data adds nothing to it.

Sparse foreign keys use filtered indexes as well. In a restore of the production database, three nullable foreign keys on the transactions table were NULL on every row. Their unfiltered indexes held an entry for every row and were maintained on every insert and delete without serving a read. Filtered to IS NOT NULL, they hold only the rows that carry a value and still serve lookups by those keys, because an equality test never matches NULL.

A test that reads the SQL

ToQueryString returns the SQL that EF Core would send for a query, without running it. A unit test builds the context on the SQL Server provider with no database behind it and renders every tenant-scoped set in both modes:

for each of the 14 tenant-scoped sets:
    context = new context, no active organization
    personal_sql     = set.ToQueryString()
    set an active organization on the same context
    organization_sql = set.ToQueryString()
    assert neither contains " OR " nor a mode parameter

For the transactions set, the test also asserts the exact predicate of each mode and that the SQL changes when the organization changes on the same context. An EF Core upgrade that kept the flag as a parameter, or evaluated it once per context, would fail this test instead of reaching production as a scan.

The same rule applies outside the global filter. A repository that wrote its own tenant scope as an OR over both modes now chooses the predicate in C# before building the query.

Checklist

• Keep each tenancy mode’s predicate in its own statement; an OR over mode parameters leaves the optimizer one plan for both.
• Inline only the value that selects a branch. Keep owner and organization identifiers as parameters, so each query has at most one statement per mode.
• Read the mode from the context on every query, and test that changing the mode on a live context changes the SQL.
• Key each branch’s index on its tenant column followed by the sort order of the lists it serves, and include the other tenant column.
• Filter indexes on sparse nullable foreign keys to non-null rows.
• Pin the generated SQL of every filtered set in every mode with ToQueryString in a unit test.
• Apply the same split to tenant scopes written by hand outside the global filter.

Why this design

Both tenancy modes share one schema and one EF Core context, and the global filters traverse navigations between densely linked entities. Historical rows had to stay as they were, with no data migration, and the rows visible in each mode could not change. Those constraints put the fix in the filter definitions and the indexes.

The design has limits. Filtered queries skip EF Core’s command cache, so the last step of SQL generation runs on every execution. The pruning depends on how EF Core processes EF.Constant inside a query filter, which the documentation does not describe; the SQL test is what makes it safe to upgrade. The test pins the shape of the SQL, not the plan, so the choice of a seek is still the optimizer’s and rests on the indexes above. SQL Server holds two plans per filtered query shape.

Alternative	What it adds	What it costs
One tenant column, with each personal user as a one-member organization	One predicate, one index and one plan per query, with no mode at all	A migration of every historical row, and personal-only features re-expressed as membership
A separate context type or model per mode	A filter per mode with no dependency on how EF.Constant is processed	Two models in memory, a choice of context per request, and no switch once a context exists
OPTION (RECOMPILE) on filtered queries	SQL Server sees the parameter values at compile time and can drop the dead branch itself	A plan compilation on every execution, paid in database CPU
Tenant predicates written by hand in every repository	Full control of each statement	A new query can forget the tenant scope, which is what a global filter prevents

Further reading

• Global Query Filters, EF Core documentation. How filters are defined, how they use context data for multi-tenancy, and how they interact with navigations.
• Query Processing Architecture Guide, SQL Server documentation. Plan caching and reuse per statement text, and parameter sensitivity with OPTION (RECOMPILE).
• Create Filtered Indexes, SQL Server documentation. Indexes on a subset of rows, including columns that are mostly NULL.
• Multitenant SaaS Patterns, Azure SQL documentation. The tenancy models a shared multitenant schema is chosen from.