EF Core Global Query Filters: Enforce Row‑Level Security with a Single Predicate

What Are Global Query Filters?

EF Core Global Query Filters let you attach a predicate to an entity type that the framework automatically applies to every query. The filter is translated to SQL and therefore works for LINQ, FromSqlRaw, and Include navigation properties. It is not applied to client‑side collections or raw SQL executed with ExecuteSqlRaw.

Defining a Filter in OnModelCreating

Filters are configured in the DbContext’s OnModelCreating method using HasQueryFilter. The lambda must reference only properties that can be translated to SQL and values that are request‑scoped.

public class MyDbContext : DbContext
{
    private readonly ITenantService _tenantService;

    public MyDbContext(DbContextOptions options, ITenantService tenantService)
        : base(options) => _tenantService = tenantService;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Soft‑delete and tenant isolation filter
        modelBuilder.Entity<Blog>()
            .HasQueryFilter(b => !b.IsDeleted && b.TenantId == _tenantService.CurrentTenantId);
    }
}

In this example ITenantService is a scoped service that returns the tenant ID for the current request. Using a scoped value instead of a static field prevents cross‑request leakage.

Practical Example: Tenant Isolation & Soft Delete

Assume a Blog entity:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int TenantId { get; set; }
    public bool IsDeleted { get; set; }
}

With the filter above, any query such as:

var blogs = await context.Blogs.ToListAsync();

automatically translates to:

SELECT * FROM Blogs
WHERE IsDeleted = 0 AND TenantId = @__tenantId_0

where @__tenantId_0 is the value from ITenantService.CurrentTenantId. The same filter applies to Include:

var blogs = await context.Blogs
    .Include(b => b.Posts)
    .ToListAsync();

EF Core will add the filter to the JOIN that loads Posts, ensuring only posts belonging to the current tenant are returned.

How EF Translates Filters to SQL

• Predicate is parsed during model building.
• Only properties that map to columns and values that are constants or parameters are allowed.
• The resulting SQL contains a WHERE clause or ON clause for joins.

Because the filter is part of the query plan, it is applied on the server side, which keeps client memory usage low and protects against accidental data leakage.

Common Pitfalls and How to Avoid Them

1. Using static or thread‑local state
   Static fields can hold a tenant ID that leaks between requests. Always inject a scoped service and keep the value request‑scoped.
2. Referencing navigation properties
   Filters that reference navigation properties cause EF to generate additional joins. If the navigation is optional, the filter may produce a Cartesian product or throw a translation exception.
3. Non‑translatable expressions
   Using .NET methods (e.g., DateTime.Now or Guid.NewGuid()) inside the filter will throw a runtime exception. Stick to property comparisons and simple logical operators.
4. Forgetting that raw SQL bypasses filters
   context.Database.ExecuteSqlRaw("SELECT * FROM Blogs"); will not apply the filter. Add the predicate manually when using raw SQL.
5. Debugging confusion
   Since the filter is applied automatically, a developer may think a query is returning no rows because of a bug in the LINQ expression, when actually the filter is filtering them out. Inspect the generated SQL via UseLoggerFactory or ToQueryString to confirm the filter is present.

Testing and Verifying Your Filters

Unit tests are the safest way to confirm filter logic:

[Fact]
public async Task GlobalFilter_ExcludesDeleted_Blogs()
{
    var options = new DbContextOptionsBuilder<MyDbContext>()
        .UseInMemoryDatabase("TestDb")
        .Options;
    var tenantService = new TestTenantService(1);
    using var ctx = new MyDbContext(options, tenantService);

    ctx.Blogs.Add(new Blog { Title = "A", TenantId = 1, IsDeleted = false });
    ctx.Blogs.Add(new Blog { Title = "B", TenantId = 1, IsDeleted = true });
    await ctx.SaveChangesAsync();

    var blogs = await ctx.Blogs.ToListAsync();
    Assert.Single(blogs);
    Assert.Equal("A", blogs[0].Title);
}

For integration testing, enable EF Core logging:

services.AddDbContext<MyDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
           .EnableSensitiveDataLogging());

Run a query and inspect the console output to see the WHERE clause containing IsDeleted = 0 AND TenantId = @__tenantId_0.

Limitations

• Filters are not applied to FromSqlRaw or ExecuteSqlRaw calls.
• They cannot reference client‑side collections or complex types that EF cannot translate.
• Changing the filter after the model is built requires a new DbContext instance.

Takeaway

Global Query Filters provide a clean, declarative way to enforce row‑level security or soft‑delete logic across an application. Define them once in OnModelCreating, keep the filter expression simple and request‑scoped, and verify via generated SQL or unit tests to avoid hidden data leaks.