HasQueryFilter

Add model-level filters for soft delete and tenancy with HasQueryFilter, name them in EF Core 10, and switch one off without dropping the others.

Signature

public virtual EntityTypeBuilder<TEntity> HasQueryFilter(Expression<Func<TEntity, bool>>? filter)
public virtual EntityTypeBuilder<TEntity> HasQueryFilter(string filterKey, Expression<Func<TEntity, bool>>? filter)

Added in .NET EF Core 2.0

Parameters

Parameter	Type	Description
`$filterKey`optional	`string`= `none (the single unnamed filter)`	EF Core 10: the filter's name. Several named filters can be defined on one entity, and IgnoreQueryFilters(["Name"]) disables only the ones listed.
`$filter`	`Expression<Func<TEntity, bool>>?`	A predicate over the entity. It may reference properties of the DbContext, which are read for each query and sent as parameters.

Return value

EntityTypeBuilder<TEntity>The same builder, so further configuration can be chained. The filter becomes part of the model and is added to every LINQ query of the entity type.

Errors

Nothing when configured. Filters on the base type only: configuring one on a derived entity type in an inheritance hierarchy fails model validation.

Changelog

PHP EF Core 10.0Named filters: HasQueryFilter("SoftDelete", ...) and IgnoreQueryFilters(["SoftDelete"]). Before 10.0 an entity had one filter and a second HasQueryFilter call replaced the first.

HasQueryFilter attaches a predicate to an entity type in the model. EF Core then adds it to every LINQ query whose results include that type: queries on the DbSet, entities reached through Include, navigations used in projections, FromSql composed with LINQ, and the WHERE of ExecuteUpdateAsync/ExecuteDeleteAsync. Raw SQL from ExecuteSqlAsync and SqlQuery is not filtered.

Reading per-request values

The model is built once per context type, so a filter cannot capture a tenant id when it is built. Reference a property or field of the context instead: o => o.TenantId == TenantId. EF Core recognises the context member and reads it from the current instance on every query, sending it as a parameter. The value must be set before the first query; with AddDbContext, inject a tenant accessor into the context's constructor. Pooled contexts cannot take scoped services, so a pooled context needs the value assigned after it is leased.

Named filters (EF Core 10)

Before EF Core 10 an entity had one filter. Calling HasQueryFilter twice silently kept only the second, so soft delete and tenancy had to be combined with &&, and IgnoreQueryFilters(), used to show a recycle bin, removed the tenant check too and leaked other tenants' rows. Named filters fix both:

Call	Effect
`HasQueryFilter("SoftDelete", o => !o.IsDeleted)`	Adds a filter named SoftDelete
`HasQueryFilter("Tenant", o => o.TenantId == TenantId)`	Adds a second, independent filter
`IgnoreQueryFilters(["SoftDelete"])`	Disables only SoftDelete for this query
`IgnoreQueryFilters()`	Disables every filter, tenant included

Use names on every entity once you have more than one concern, and do not mix named and unnamed filters on the same type. Keep the names in constants so a typo does not silently ignore nothing.

Required navigations

If Order has a filter and OrderLine has a required navigation to it, a query on lines that joins orders uses an INNER JOIN, so lines of filtered-out orders disappear, while a query on lines alone still returns them. EF Core logs a warning about this when it builds the model. Give the dependent a matching filter (l => !l.Order.IsDeleted) or make the navigation optional, so both paths agree.

Production failure modes

Tenant leak through IgnoreQueryFilters. The admin endpoint meant to include deleted orders lists every tenant's orders. Named filters and a code review rule against parameterless IgnoreQueryFilters() prevent it.
Index misses. Every query now carries TenantId = @p AND NOT IsDeleted; indexes must lead with TenantId or queries scan.
Raw SQL bypass. A reporting query in SqlQuery returns all tenants because filters only apply to LINQ.

Example

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddAuthentication().AddJwtBearer();
// No tenant_id claim: 403 from authorization, before any query can run.
builder.Services.AddAuthorizationBuilder()
    .AddPolicy("tenant", p => p.RequireAuthenticatedUser().RequireClaim("tenant_id"));
builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ITenantContext, ClaimsTenantContext>();
// AddDbContext, not the pool: the context takes a scoped ITenantContext.
builder.Services.AddDbContext<ShopDb>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Shop")));
var app = builder.Build();

var orders = app.MapGroup("/orders").RequireAuthorization("tenant");

// Both filters apply: this tenant's orders that are not deleted.
orders.MapGet("", (ShopDb db, CancellationToken ct) =>
    db.Orders.AsNoTracking().OrderByDescending(o => o.Id).Take(50)
        .Select(o => new OrderRow(o.Id, o.Total, o.IsDeleted)).ToListAsync(ct));

// Recycle bin: lifts SoftDelete only. The tenant filter still applies.
orders.MapGet("/deleted", (ShopDb db, CancellationToken ct) =>
    db.Orders.AsNoTracking()
        .IgnoreQueryFilters([ShopDb.SoftDeleteFilter])
        .Where(o => o.IsDeleted)
        .Select(o => new OrderRow(o.Id, o.Total, o.IsDeleted)).ToListAsync(ct));

app.Run();

public record OrderRow(int Id, decimal Total, bool IsDeleted);

public interface ITenantContext
{
    Guid TenantId { get; }
}

public sealed class ClaimsTenantContext(IHttpContextAccessor accessor) : ITenantContext
{
    // Read from the authenticated user's tenant_id claim, never from a client-supplied header.
    // The "tenant" policy guarantees the claim exists, so this only throws on a malformed value.
    public Guid TenantId =>
        Guid.TryParse(accessor.HttpContext?.User.FindFirst("tenant_id")?.Value, out var id)
            ? id
            : throw new InvalidOperationException("No valid tenant on the current request.");
}

public class Order
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public decimal Total { get; set; }
    public bool IsDeleted { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options, ITenantContext tenant) : DbContext(options)
{
    public const string SoftDeleteFilter = "SoftDelete";
    public const string TenantFilter = "Tenant";

    // Read on every query and sent as a parameter; the model itself is cached.
    public Guid TenantId => tenant.TenantId;

    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(b =>
        {
            b.HasQueryFilter(SoftDeleteFilter, o => !o.IsDeleted);
            b.HasQueryFilter(TenantFilter, o => o.TenantId == TenantId);
            b.HasIndex(o => new { o.TenantId, o.IsDeleted });
        });
    }
}

When to use it

Hiding soft-deleted orders from every query while a recycle-bin endpoint lifts only the SoftDelete filter
Enforcing tenant isolation in a shared-schema multi-tenant API with a filter that reads the tenant id from the context
Upgrading a pre-EF Core 10 combined filter to named filters so admin queries can no longer leak other tenants' data

Frequently asked questions

Can I have two query filters on one entity?

With EF Core 10, yes: give each a name with HasQueryFilter("Name", ...). Before EF Core 10, a second HasQueryFilter call replaced the first, so you combined them with && in one filter.

Do query filters apply to raw SQL?

To FromSql and FromSqlRaw results, yes, because EF Core composes LINQ over them, including the filter. Not to ExecuteSqlAsync or Database.SqlQuery, which never go through the entity's query pipeline.

Does the filter value get baked into the model?

Only if you capture a local value or a constant. Reference a property of the DbContext instead; EF Core reads it from the current context instance for each query and sends it as a parameter, so one cached model serves every tenant.

More examples

Before EF Core 10: one combined filter

This is the shape most existing code bases have. When upgrading to EF Core 10, split the combined filter into named filters and replace each parameterless <code>IgnoreQueryFilters()</code> with the list of names it really meant.

using Microsoft.EntityFrameworkCore;

public class Invoice
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public bool IsDeleted { get; set; }
}

public class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public Guid TenantId { get; set; }

    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Wrong: the second unnamed call replaces the first, so only the tenant filter remains.
        // modelBuilder.Entity<Invoice>().HasQueryFilter(i => !i.IsDeleted);
        // modelBuilder.Entity<Invoice>().HasQueryFilter(i => i.TenantId == TenantId);

        // The pre-10 workaround: one filter for both concerns.
        modelBuilder.Entity<Invoice>().HasQueryFilter(i => !i.IsDeleted && i.TenantId == TenantId);
    }
}

public sealed class InvoiceAdmin(BillingDb db)
{
    // Dangerous with a combined filter: lifts tenant isolation along with soft delete.
    public Task<List<Invoice>> DeletedLeaky(CancellationToken ct) =>
        db.Invoices.IgnoreQueryFilters().Where(i => i.IsDeleted).ToListAsync(ct);

    // Safe without named filters: restate the tenant check by hand.
    public Task<List<Invoice>> Deleted(CancellationToken ct) =>
        db.Invoices.IgnoreQueryFilters().Where(i => i.IsDeleted && i.TenantId == db.TenantId).ToListAsync(ct);
}

Matching filter on a dependent with a required navigation

Giving both entities a filter with the same name means <code>IgnoreQueryFilters(["SoftDelete"])</code> lifts it consistently on both. The dependent's filter costs a join to <code>Orders</code>, so check that the foreign key is indexed.

using Microsoft.EntityFrameworkCore;

public class Order
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public List<OrderLine> Lines { get; } = [];
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;   // required
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().HasQueryFilter("SoftDelete", o => !o.IsDeleted);

        // Without this, db.OrderLines returns lines of deleted orders, but
        // db.OrderLines.Include(l => l.Order) drops them: two answers to one question.
        modelBuilder.Entity<OrderLine>().HasQueryFilter("SoftDelete", l => !l.Order.IsDeleted);
    }
}

public sealed class SkuDemand(ShopDb db)
{
    public Task<int> UnitsOrdered(string sku, CancellationToken ct) =>
        db.OrderLines.Where(l => l.Sku == sku).SumAsync(l => l.Quantity, ct);
}

Test: tenant isolation holds

This is the regression test worth having for every multi-tenant entity: it fails the day someone replaces a named <code>IgnoreQueryFilters</code> call with the parameterless one, or forgets the tenant filter on a new entity.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Xunit;

public sealed class TenantFilterTests
{
    private static readonly Guid Acme = Guid.Parse("6f1c2a52-6f7e-4f39-9a51-1b7c3c1d9e01");
    private static readonly Guid Globex = Guid.Parse("0b8d4e2a-3c5f-4a6b-8d9e-2f1a3b4c5d02");

    [Fact]
    public async Task A_tenant_never_sees_another_tenants_orders_even_in_the_recycle_bin()
    {
        var ct = TestContext.Current.CancellationToken;
        await using var connection = new SqliteConnection("Data Source=:memory:");
        await connection.OpenAsync(ct);
        var options = new DbContextOptionsBuilder<ShopDb>().UseSqlite(connection).Options;

        await using (var seed = new ShopDb(options) { TenantId = Acme })
        {
            await seed.Database.EnsureCreatedAsync(ct);
            seed.Orders.AddRange(
                new Order { TenantId = Acme, IsDeleted = true },
                new Order { TenantId = Globex, IsDeleted = true });
            await seed.SaveChangesAsync(ct);
        }

        await using var db = new ShopDb(options) { TenantId = Acme };
        var deleted = await db.Orders.IgnoreQueryFilters(["SoftDelete"]).ToListAsync(ct);

        Assert.All(deleted, o => Assert.Equal(Acme, o.TenantId));
        Assert.Single(deleted);
    }
}

public class Order
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public bool IsDeleted { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public Guid TenantId { get; init; }
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Order>()
            .HasQueryFilter("SoftDelete", o => !o.IsDeleted)
            .HasQueryFilter("Tenant", o => o.TenantId == TenantId);
}

Discussion

Be the first to comment on this lesson.

Parameters

Return value

Errors

Changelog

See also

Reading per-request values

Named filters (EF Core 10)

Required navigations

Production failure modes

Example

When to use it

Frequently asked questions

Can I have two query filters on one entity?

Do query filters apply to raw SQL?

Does the filter value get baked into the model?

More examples

Before EF Core 10: one combined filter

Matching filter on a dependent with a required navigation

Test: tenant isolation holds

Related

Discussion