Optimizing Global Filters

In this lesson, you will learn how to identify and mitigate the performance bottlenecks that occur when global query filters are applied to large-scale database tables.

The Performance Cost of Global Filters

Global query filters are a powerful feature in EF Core, commonly used for "soft deletes" or multi-tenancy. You define them in your OnModelCreating method like this:

modelBuilder.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);

While convenient, these filters are injected into every single query executed against that entity. When your Order table grows to millions of rows, the database must perform a table scan or index scan to apply this filter every time, even if you are just looking for a single record by ID.

Why Performance Degrades

When a global filter is applied, the database engine must resolve the filter expression during the query compilation phase. If the column used in your filter (e.g., IsDeleted) is not properly indexed, the database engine ignores potential seek operations and defaults to a full scan.

Furthermore, if your filter logic includes complex expressions or joins to other tables—for instance, checking a TenantId against a user session—this overhead is multiplied across every query. As the complexity of the filter grows, the cost of the query increases, leading to higher latency and increased CPU consumption on the database server.

Strategies for Optimization

To ensure your global filters don't cripple your application as it scales:

1. Index the Filter Columns: Always ensure that columns used in a HasQueryFilter are included in your database indexes. If you filter by IsDeleted, ensure a non-clustered index exists on that column.
2. Keep Logic Simple: Avoid performing complex calculations or subqueries inside the filter definition. The more logic the database engine has to evaluate for every row, the slower the retrieval becomes.
3. Use IgnoreQueryFilters() Sparingly: If you have an admin dashboard or a background process that needs to traverse the entire dataset, use the .IgnoreQueryFilters() operator to bypass the performance penalty of the global filter.

Key Takeaway: Global filters are applied to every query, so always index the involved columns to transform expensive table scans into efficient index seeks.