Named Query Filters (EF Core 10)

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
    DateTimeOffset? DeletedAt { get; set; }
}

public class Product : ISoftDeletable
{
    public int Id { get; set; }
    public string Sku { get; set; } = "";
    public string Name { get; set; } = "";
    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
}

public sealed class SoftDeleteInterceptor(TimeProvider clock) : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(DbContextEventData eventData, InterceptionResult<int> result)
    {
        ConvertDeletes(eventData.Context);
        return result;
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        ConvertDeletes(eventData.Context);
        return ValueTask.FromResult(result);
    }

    private void ConvertDeletes(DbContext? context)
    {
        if (context is null) return;
        foreach (var entry in context.ChangeTracker.Entries<ISoftDeletable>().Where(e => e.State == EntityState.Deleted))
        {
            // Remove() becomes an UPDATE that hides the row.
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = clock.GetUtcNow();
        }
    }
}

public class CatalogDb(DbContextOptions<CatalogDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(product =>
        {
            product.HasQueryFilter("SoftDelete", p => !p.IsDeleted);
            // Only live rows compete for the SKU, so a deleted product can be recreated.
            product.HasIndex(p => p.Sku).IsUnique().HasFilter("\"IsDeleted\" = false");
        });
    }
}

using Microsoft.EntityFrameworkCore;

public class Invoice
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public decimal Amount { get; set; }
}

public interface ITenantContext
{
    Guid TenantId { get; }
}

// Wrong: OnModelCreating runs once per process. The first request's tenant is captured
// into the cached model and every later request, for every tenant, is filtered by it.
public class LeakyBillingDb(DbContextOptions<LeakyBillingDb> options, ITenantContext tenant) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var tenantId = tenant.TenantId;
        modelBuilder.Entity<Invoice>().HasQueryFilter("Tenant", i => i.TenantId == tenantId);
    }
}

// Right: the filter reads a member of the context, which EF parameterises per instance.
public class BillingDb(DbContextOptions<BillingDb> options, ITenantContext tenant) : DbContext(options)
{
    private Guid CurrentTenantId => tenant.TenantId;

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

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Invoice>().HasQueryFilter("Tenant", i => i.TenantId == CurrentTenantId);
}

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

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; set; }
    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);
}

public class TenantFilterTests
{
    private static readonly Guid Acme = Guid.Parse("0b6c9f4e-5d1a-4c2b-9a51-6f0e2d3c4b11");
    private static readonly Guid Globex = Guid.Parse("7d2e8a10-3f4b-4e6c-8b9d-1a2b3c4d5e66");

    [Fact]
    public async Task Ignoring_soft_delete_keeps_the_tenant_filter()
    {
        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))
        {
            await seed.Database.EnsureCreatedAsync(ct);
            seed.Orders.AddRange(
                new Order { TenantId = Acme },
                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 };

        Assert.Equal(1, await db.Orders.CountAsync(ct));
        Assert.Equal(2, await db.Orders.IgnoreQueryFilters(["SoftDelete"]).CountAsync(ct));
        Assert.Equal(3, await db.Orders.IgnoreQueryFilters().CountAsync(ct));
    }
}