Global Query Filters
Overview
Global query filters automatically apply WHERE clauses to all queries for an entity. Perfect for soft delete, multi-tenancy, and active/inactive records.
Soft Delete Pattern
Setup
// Add IsDeleted to entities
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public bool IsDeleted { get; set; } // Soft delete flag
}
// Configure global filter
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.HasQueryFilter(p => !p.IsDeleted);
}
Usage
// All queries automatically filtered
var products = await context.Products.ToListAsync();
// SELECT * FROM Products WHERE IsDeleted = 0
// No deleted products returned!
var product = await context.Products.FindAsync(1); // null if deleted
// Delete operation becomes update
var product = await context.Products.FindAsync(1);
context.Products.Remove(product);
await context.SaveChangesAsync();
// UPDATE Products SET IsDeleted = 1 WHERE Id = 1
Ignoring Filter
// Include deleted records when needed
var allProducts = await context.Products
.IgnoreQueryFilters()
.ToListAsync();
// SELECT * FROM Products (no WHERE clause)
// Useful for admin interfaces or restore functionality
Multi-Tenancy Pattern
Setup
public class Order
{
public int Id { get; set; }
public int TenantId { get; set; }
public decimal Amount { get; set; }
}
public class AppDbContext : DbContext
{
private readonly IHttpContextAccessor _httpContextAccessor;
public AppDbContext(
DbContextOptions options,
IHttpContextAccessor httpContextAccessor) : base(options)
{
_httpContextAccessor = httpContextAccessor;
}
private int GetCurrentTenantId()
{
var claim = _httpContextAccessor.HttpContext?.User
.FindFirst("TenantId");
return int.Parse(claim?.Value ?? "0");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Order>()
.HasQueryFilter(o => o.TenantId == GetCurrentTenantId());
}
}
Usage
// Tenant 1 user queries
var orders = await context.Orders.ToListAsync();
// SELECT * FROM Orders WHERE TenantId = 1
// Tenant 2 user queries
var orders = await context.Orders.ToListAsync();
// SELECT * FROM Orders WHERE TenantId = 2
// Complete isolation!
Active/Inactive Pattern
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public bool IsActive { get; set; }
}
modelBuilder.Entity<Product>()
.HasQueryFilter(p => p.IsActive);
// All queries return only active products
var products = await context.Products.ToListAsync();
Combining Multiple Filters
// Combine soft delete + active status
modelBuilder.Entity<Product>()
.HasQueryFilter(p => !p.IsDeleted && p.IsActive);
// Combine soft delete + tenant
modelBuilder.Entity<Order>()
.HasQueryFilter(o => !o.IsDeleted && o.TenantId == GetCurrentTenantId());
Relationship Considerations
// Filter applied to navigations too!
var category = await context.Categories
.Include(c => c.Products) // Only non-deleted products loaded
.FirstAsync();
// Be careful with required relationships
public class Order
{
public int CustomerId { get; set; }
public Customer Customer { get; set; } // Required
}
modelBuilder.Entity<Customer>()
.HasQueryFilter(c => !c.IsDeleted);
// This can fail if customer is deleted but order is not!
var order = await context.Orders
.Include(o => o.Customer) // Customer might be filtered out
.FirstAsync();
// Customer navigation will be null even though FK exists
Implementation Patterns
Base Entity Approach
public abstract class SoftDeletableEntity
{
public bool IsDeleted { get; set; }
public DateTime? DeletedAt { get; set; }
public string DeletedBy { get; set; }
}
public class Product : SoftDeletableEntity
{
public int Id { get; set; }
public string Name { get; set; }
// IsDeleted inherited
}
// Apply filter to all soft-deletable entities
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
if (typeof(SoftDeletableEntity).IsAssignableFrom(entityType.ClrType))
{
var parameter = Expression.Parameter(entityType.ClrType, "e");
var property = Expression.Property(parameter, nameof(SoftDeletableEntity.IsDeleted));
var filter = Expression.Lambda(Expression.Not(property), parameter);
entityType.SetQueryFilter(filter);
}
}
Tenant-Aware Base Entity
public interface ITenantEntity
{
int TenantId { get; set; }
}
public class Order : ITenantEntity
{
public int Id { get; set; }
public int TenantId { get; set; }
}
// Apply to all tenant entities
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
if (typeof(ITenantEntity).IsAssignableFrom(entityType.ClrType))
{
var method = typeof(AppDbContext)
.GetMethod(nameof(GetTenantFilter), BindingFlags.NonPublic | BindingFlags.Static)
.MakeGenericMethod(entityType.ClrType);
var filter = method.Invoke(null, new object[] { this });
entityType.SetQueryFilter((LambdaExpression)filter);
}
}
private static LambdaExpression GetTenantFilter<TEntity>(AppDbContext context)
where TEntity : class, ITenantEntity
{
Expression<Func<TEntity, bool>> filter = e => e.TenantId == context.GetCurrentTenantId();
return filter;
}
SaveChanges Override for Soft Delete
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
// Intercept deletions
foreach (var entry in ChangeTracker.Entries())
{
if (entry.State == EntityState.Deleted &&
entry.Entity is SoftDeletableEntity entity)
{
// Convert to update
entry.State = EntityState.Modified;
entity.IsDeleted = true;
entity.DeletedAt = DateTime.UtcNow;
entity.DeletedBy = _currentUser.Id;
}
}
return await base.SaveChangesAsync(cancellationToken);
}
Hard Delete When Needed
// Force hard delete
public async Task HardDeleteAsync\<T\>(T entity) where T : class
{
context.Entry(entity).State = EntityState.Deleted;
// Skip soft delete interceptor
var entry = context.Entry(entity);
entry.Property("IsDeleted").CurrentValue = null;
entry.Property("IsDeleted").IsModified = false;
await context.SaveChangesAsync();
}
Performance Considerations
// Filters add WHERE clauses to every query
var product = await context.Products.FindAsync(1);
// SELECT * FROM Products WHERE Id = 1 AND IsDeleted = 0
// Indexes needed for filter columns!
modelBuilder.Entity<Product>()
.HasIndex(p => p.IsDeleted)
.HasFilter("[IsDeleted] = 0"); // Filtered index
// Or composite indexes
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.TenantId, o.OrderDate })
.HasFilter("[IsDeleted] = 0");
Testing Query Filters
[Fact]
public async Task QueryFilter_FiltersDeletedProducts()
{
// Arrange
var product = new Product { Name = "Test", IsDeleted = true };
_context.Products.Add(product);
await _context.SaveChangesAsync();
_context.ChangeTracker.Clear();
// Act
var products = await _context.Products.ToListAsync();
// Assert
Assert.Empty(products); // Deleted product not returned
}
[Fact]
public async Task IgnoreQueryFilters_ReturnsDeletedProducts()
{
// Arrange
var product = new Product { Name = "Test", IsDeleted = true };
_context.Products.Add(product);
await _context.SaveChangesAsync();
_context.ChangeTracker.Clear();
// Act
var products = await _context.Products
.IgnoreQueryFilters()
.ToListAsync();
// Assert
Assert.Single(products); // Deleted product returned
}
Interview Questions
Q: What are global query filters?
A: Global query filters automatically apply WHERE clauses to all queries for an entity. Configured once in OnModelCreating, they ensure consistent filtering without manual WHERE clauses.
Q: What are common use cases for global query filters?
A:
- Soft Delete: Filter out IsDeleted = true records
- Multi-Tenancy: Filter by TenantId for data isolation
- Active/Inactive: Only show IsActive = true records
- Row-Level Security: Filter by user permissions
Q: How do you bypass a global query filter?
A: Use IgnoreQueryFilters() in the query:
var all = await context.Products.IgnoreQueryFilters().ToListAsync();
Q: What happens to navigation properties with query filters?
A: Filters apply to navigations too. If a related entity is filtered out, the navigation property will be null even if the foreign key exists.