Global Query Filters in EF Core: Soft Delete, Multi-Tenancy and IgnoreQueryFilters

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: Where Does the Deleted Book Disappear From?

Frank Herbert has two books: Dune and Dune Messiah. Each has one order item. We take Dune Messiah off sale, but instead of actually deleting the row, we set its IsDeleted flag. We will build the model and the filter in a moment; for now, guess what these three queries return:

using (var db = new BookstoreContext())
{
    var books = await db.Books.CountAsync();
    var authors = await db.Authors.Select(a => new { a.Name, BookCount = a.Books.Count }).ToListAsync();
    var items = await db.OrderItems.Include(i => i.Book).ToListAsync();

    Console.WriteLine($"Books: {books}");
    Console.WriteLine($"Frank Herbert's books: {authors[0].BookCount}");
    Console.WriteLine($"Order items: {items.Count}");
}

Saying 1 for the book count is easy. The author's book count? And the order items? There are two items, and we did not delete any of them. The answers carry the most important trap of this post.

Here is the model:

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool IsDeleted { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class OrderItem
{
    public int Id { get; set; }
    public int Quantity { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
}

Soft Delete with HasQueryFilter

Soft delete means marking a row as "deleted" instead of removing it. It is a common approach for data whose history must be kept, such as orders, invoices and audit records. Its problem is well known too: you now have to remember to write Where(b => !b.IsDeleted) in every query, and the moment someone forgets, deleted books show up on the site.

A global query filter lets you write this condition into the model once and have EF Core add it to every query:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>().HasQueryFilter(b => !b.IsDeleted);
}

Now every query written against db.Books contains this condition. The SQL of the simplest query:

SELECT "b"."Id", "b"."AuthorId", "b"."IsDeleted", "b"."Title"
FROM "Books" AS "b"
WHERE NOT ("b"."IsDeleted")

A filter can take any condition you can write in LINQ and can access members of the context; we will use that for multi-tenancy shortly. Filters are defined on an entity type, and if you use inheritance they can only be placed on the root type of the hierarchy.

Turning Remove into a Soft Delete

The filter solves the read side. On the write side, db.Books.Remove(book) still produces a DELETE. We want the rest of the code to keep using a normal Remove, while an UPDATE goes to the database. For that we override SaveChangesAsync:

public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    foreach (var entry in ChangeTracker.Entries<Book>().Where(e => e.State == EntityState.Deleted))
    {
        entry.State = EntityState.Modified; // UPDATE instead of DELETE
        entry.Entity.IsDeleted = true;
    }

    return base.SaveChangesAsync(cancellationToken);
}

The delete code from the opening was this:

using (var db = new BookstoreContext())
{
    var messiah = await db.Books.SingleAsync(b => b.Title == "Dune Messiah");
    db.Books.Remove(messiah);
    await db.SaveChangesAsync();
}

There is no DELETE in the EF Core 10 log:

info: 
      Executed DbCommand (0ms) [Parameters=[@p3='?' (DbType = Int32), @p0='?' (DbType = Int32), @p1='?' (DbType = Boolean), @p2='?' (Size = 12)], CommandType='Text', CommandTimeout='30']
      UPDATE "Books" SET "AuthorId" = @p0, "IsDeleted" = @p1, "Title" = @p2
      WHERE "Id" = @p3
      RETURNING 1;

Because we switched the state to Modified, EF Core updates every column. That is not a problem, but if you like you can mark only IsDeleted as modified. In the interceptors post we will do the same job with a SaveChangesInterceptor, without cluttering the context. That is also the right place to record when the row was deleted and by whom.

Now the output of the three opening queries:

Books: 1
Frank Herbert's books: 1
Order items: 1

The first two results are as expected. The filter is also applied to a.Books.Count, which goes through a navigation:

      SELECT "a"."Name", (
          SELECT COUNT(*)
          FROM "Books" AS "b"
          WHERE NOT ("b"."IsDeleted") AND ("a"."Id" = "b"."AuthorId")) AS "BookCount"
      FROM "Authors" AS "a"

But the third result lost an order item. We did not delete any item. We will come back to this two sections from now.

IgnoreQueryFilters: The Trash Page

Sometimes you really need the rows the filter hides: an admin's "deleted items" page or an operation that restores a book. IgnoreQueryFilters() turns off all global filters for that query:

var trash = await db.Books
    .IgnoreQueryFilters()
    .Where(b => b.IsDeleted)
    .ToListAsync();

SELECT "b"."Id", "b"."AuthorId", "b"."IsDeleted", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."IsDeleted"
Trash: Dune Messiah

You now write the condition yourself; EF Core only added your Where. Keep in mind that in EF Core 6, IgnoreQueryFilters is all or nothing. If an entity has both a soft delete filter and a tenant filter, both are turned off. Named filters, which let you turn off only one of them, came with EF Core 10; we will see them in the version posts.

The filter also kicks in at some unexpected places. If the entity is not in the tracker, Find sends a query to the database, and that query goes through the filter as well:

var found = await db.Books.FindAsync(2); // the id of Dune Messiah
Console.WriteLine(found is null ? "Find: null" : $"Find: {found.Title}");

Find: null

The book is still in the database but behaves as if it did not exist. If you write the restore operation with Find, it does not work; you have to query with IgnoreQueryFilters().

Multi-Tenancy: A Filter per Context

You sell the same application to several bookstores, and all their data lives in the same database. Each bookstore must see only its own books. This is the most valuable use of global filters, because a forgotten Where here is not a small bug as with soft delete, it is a data leak:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool IsDeleted { get; set; }
    public string TenantId { get; set; } = "";
}

public class BookstoreContext : DbContext
{
    private readonly string _tenantId;

    public BookstoreContext(string tenantId) => _tenantId = tenantId;

    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=tenants.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .HasQueryFilter(b => !b.IsDeleted && b.TenantId == _tenantId);
    }
}

The _tenantId inside the filter is not a constant but a field of the context. EF Core notices this and puts it into the query as a parameter. Even though the model is built once and cached, every context sends its own value:

using (var harbor = new BookstoreContext("harbor"))
using (var corner = new BookstoreContext("corner"))
{
    Console.WriteLine(harbor.Books.ToQueryString());
    Console.WriteLine();
    Console.WriteLine("harbor: " + string.Join(", ", await harbor.Books.Select(b => b.Title).ToListAsync()));
    Console.WriteLine("corner: " + string.Join(", ", await corner.Books.Select(b => b.Title).ToListAsync()));
}

.param set @ef_filter___tenantId 'harbor'

SELECT "b"."Id", "b"."IsDeleted", "b"."TenantId", "b"."Title"
FROM "Books" AS "b"
WHERE NOT ("b"."IsDeleted") AND "b"."TenantId" = @ef_filter___tenantId

harbor: Dune
corner: Neuromancer

The output is from EF Core 10. On EF Core 6 the SQL is the same, only the parameter is named @__ef_filter___tenantId_0. The deleted Dune Messiah does not show up for harbor either, because we combined both conditions in the same filter.

In ASP.NET Core the tenant usually comes from the request itself: a header, a subdomain or a claim in the user's token. In that case you inject a scoped service that resolves the request's tenant into the constructor instead of a string. Remember the warning from the previous post: if you use AddDbContextPool, the context's own fields are not reset between leases, and the previous tenant's id can leak into the next request. If you use pooling with a context that has a tenant filter, you need a setup that sets the tenant again on every lease.

Interaction with Relationships: The Required Navigation Trap

Back to the missing order item. OrderItem.Book is a required relationship: every item must have a book. That is why EF Core translates Include(i => i.Book) with an INNER JOIN. But Book has a filter, and EF Core puts that filter inside the join:

SELECT "o"."Id", "o"."BookId", "o"."Quantity", "b0"."Id", "b0"."AuthorId", "b0"."IsDeleted", "b0"."Title"
FROM "OrderItems" AS "o"
INNER JOIN (
    SELECT "b"."Id", "b"."AuthorId", "b"."IsDeleted", "b"."Title"
    FROM "Books" AS "b"
    WHERE NOT ("b"."IsDeleted")
) AS "b0" ON "o"."BookId" = "b0"."Id"

When the deleted book drops out of the subquery, the item tied to it also drops out of the result because of the INNER JOIN. Nobody deleted the item, but filtering out a book made the item invisible too. Getting 2 when you count only OrderItems without Include, and 1 when you list them with Include, leads to very confusing bugs on screen. Turning the filters off and running the same query brings both items back:

var all = await db.OrderItems.IgnoreQueryFilters().Include(i => i.Book).ToListAsync();

Order items (ignore filters): 2

EF Core already notices this situation while building the model and writes a warning. Since it is logged at Warning level, anyone who keeps logs on sees it:

warn: 
      Entity 'Book' has a global query filter defined and is the required end of a relationship with the entity 'OrderItem'. This may lead to unexpected results when the required entity is filtered out. Either configure the navigation as optional, or define matching query filters for both entities in the navigation. See https://go.microsoft.com/fwlink/?linkid=2131316 for more information.

The warning suggests two ways out. The first is to make the relationship optional: the foreign key becomes nullable, EF Core uses a LEFT JOIN and the item comes back, but its Book navigation is null. The second is to put matching filters on both ends of the relationship:

modelBuilder.Entity<Book>().HasQueryFilter(b => !b.IsDeleted);
modelBuilder.Entity<OrderItem>().HasQueryFilter(i => !i.Book.IsDeleted);

Now the items of a deleted book are hidden consistently in every query, with or without Include; counting without Include now also returns 1 on both EF Core 6 and 10. Which one you choose depends on the business rule. On an order history screen, an old order's item should not disappear just because the book was taken off sale; there you either turn the filter off or mark the book with a separate state like "not for sale" instead of deleting it. This is where you see that soft delete is not the answer to everything.

Common Pitfalls

Two separate HasQueryFilter calls. Splitting the soft delete and tenant filters into two lines looks sensible:

modelBuilder.Entity<Book>().HasQueryFilter(b => !b.IsDeleted);
modelBuilder.Entity<Book>().HasQueryFilter(b => b.TenantId == "harbor"); // overrides the first one

But in EF Core 6 an entity has a single filter, and the second call replaces the first. The SQL it produces has no IsDeleted condition, and the deleted book comes back:

SELECT "b"."Id", "b"."IsDeleted", "b"."TenantId", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."TenantId" = 'harbor'
two filters: Dune, Dune Messiah

On EF Core 10 the behavior is the same for unnamed filters. Combine the conditions in a single lambda with &&.

Paths that bypass the filter. A global filter is only applied to LINQ queries. A command written with ExecuteSqlRaw knows nothing about the filter; you have to write both the tenant and the deletion condition yourself. FromSqlRaw, on the other hand, picks up the filter because it is called on a DbSet and EF Core wraps the query.

Unique indexes and soft delete. If Title has a unique index, you cannot add a new book with the same title as a deleted one; the deleted row is still in the table. The index has to be defined with a filter (HasFilter), or the uniqueness rule has to be rethought.

Forgetting the filter in tests. If your tests use a context set up with a different tenant id, you see "no data" in the test and the bug seems to be in the code. Create the test data with the right tenant.

A Small Experiment

★☆☆ Add IsDeleted to Author and apply the same soft delete filter to authors. When you delete an author, what happens in the query db.Books.Include(b => b.Author)? Which warning do you see while the model is being built?

★★☆ Write a RestoreBookAsync(int id) method: find the deleted book with IgnoreQueryFilters(), reset IsDeleted and save. Try writing the same thing with FindAsync and explain why it does not work.

★★★ Change OrderItem.BookId to int? to make the relationship optional. See that the SQL of the Include(i => i.Book) query turns into a LEFT JOIN and that Book comes back null on the deleted book's item. Decide how you would show this on an order history screen.

With global filters we learned how to quietly add a piece of the model to every query. In the next post we look at the model itself from another angle: inheritance. Different kinds of books in the same table, the discriminator column, the TPH and TPT strategies, and when to choose which.