What are global query filters and how do you use them?

Global query filters in Entity Framework Core allow you to automatically apply filtering logic to all queries for specific entity types. They're particularly useful for implementing soft deletes, multi-tenancy, and row-level security.

1. Basic Global Query Filter

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Global filter for soft deletes
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => !p.IsDeleted);
        
        modelBuilder.Entity<Category>()
            .HasQueryFilter(c => !c.IsDeleted);
    }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Usage - filter is automatically applied
var products = await context.Products.ToListAsync();
// SQL: SELECT * FROM Products WHERE IsDeleted = 0

2. Multi-Tenancy with Global Filters

public class AppDbContext : DbContext
{
    private readonly ITenantService _tenantService;

    public AppDbContext(DbContextOptions<AppDbContext> options, ITenantService tenantService)
        : base(options)
    {
        _tenantService = tenantService;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Multi-tenant filter
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => p.TenantId == _tenantService.GetCurrentTenantId());
        
        modelBuilder.Entity<Order>()
            .HasQueryFilter(o => o.TenantId == _tenantService.GetCurrentTenantId());
    }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int TenantId { get; set; }
}

public interface ITenantService
{
    int GetCurrentTenantId();
}

// Usage - automatically filters by tenant
var products = await context.Products.ToListAsync();
// SQL: SELECT * FROM Products WHERE TenantId = @currentTenantId

3. Complex Filter Conditions

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Complex filter with multiple conditions
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => 
            !p.IsDeleted && 
            p.IsActive && 
            p.PublishedAt <= DateTime.UtcNow);
    
    // Filter based on user permissions
    modelBuilder.Entity<Document>()
        .HasQueryFilter(d => 
            d.IsPublic || 
            d.OwnerId == _userService.GetCurrentUserId() ||
            d.SharedWith.Contains(_userService.GetCurrentUserId()));
}

4. Ignoring Global Filters

Sometimes you need to bypass global filters:

public class ProductService
{
    private readonly AppDbContext _context;

    // Normal query - filter is applied
    public async Task<List<Product>> GetActiveProductsAsync()
    {
        return await _context.Products.ToListAsync();
        // SQL: SELECT * FROM Products WHERE IsDeleted = 0
    }

    // Ignore global filter - get all products including deleted
    public async Task<List<Product>> GetAllProductsIncludingDeletedAsync()
    {
        return await _context.Products
            .IgnoreQueryFilters()
            .ToListAsync();
        // SQL: SELECT * FROM Products (no WHERE clause)
    }

    // Ignore specific filter for admin operations
    public async Task<List<Product>> GetProductsForAdminAsync()
    {
        return await _context.Products
            .IgnoreQueryFilters()
            .Where(p => p.IsDeleted)
            .ToListAsync();
    }
}

5. Dynamic Global Filters

Create filters that can be modified at runtime:

public class AppDbContext : DbContext
{
    private readonly ICurrentUserService _userService;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Dynamic filter based on current user
        modelBuilder.Entity<Document>()
            .HasQueryFilter(d => 
                d.IsPublic || 
                d.OwnerId == _userService.GetCurrentUserId());
    }
}

public interface ICurrentUserService
{
    int? GetCurrentUserId();
}

// Usage with different users
public class DocumentService
{
    private readonly AppDbContext _context;
    private readonly ICurrentUserService _userService;

    public async Task<List<Document>> GetUserDocumentsAsync()
    {
        // Filter automatically applies based on current user
        return await _context.Documents.ToListAsync();
    }
}

6. Global Filters with Navigation Properties

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Filter on related entities
    modelBuilder.Entity<Order>()
        .HasQueryFilter(o => 
            !o.IsDeleted && 
            o.Customer.IsActive);
    
    // Filter with multiple levels
    modelBuilder.Entity<OrderItem>()
        .HasQueryFilter(oi => 
            !oi.Order.IsDeleted && 
            !oi.Product.IsDeleted);
}

public class Order
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
    public List<OrderItem> OrderItems { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; }
}

7. Performance Considerations

public class OptimizedGlobalFilters
{
    // ✅ Good: Simple, indexed conditions
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => p.IsActive); // Simple boolean check
    }

    // ❌ Avoid: Complex calculations in filters
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => 
                p.CreatedAt.AddDays(30) > DateTime.UtcNow); // Complex calculation
    }

    // ✅ Better: Pre-calculate values
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var thirtyDaysAgo = DateTime.UtcNow.AddDays(-30);
        
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => p.CreatedAt > thirtyDaysAgo);
    }
}

8. Testing with Global Filters

public class ProductServiceTests
{
    [Test]
    public async Task GetProducts_ShouldExcludeDeletedProducts()
    {
        // Arrange
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        using var context = new AppDbContext(options);
        
        // Add test data
        context.Products.AddRange(new[]
        {
            new Product { Id = 1, Name = "Active Product", IsDeleted = false },
            new Product { Id = 2, Name = "Deleted Product", IsDeleted = true }
        });
        await context.SaveChangesAsync();

        // Act
        var products = await context.Products.ToListAsync();

        // Assert
        Assert.That(products.Count, Is.EqualTo(1));
        Assert.That(products[0].Name, Is.EqualTo("Active Product"));
    }

    [Test]
    public async Task GetAllProducts_WithIgnoreQueryFilters_ShouldReturnAll()
    {
        // Arrange
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        using var context = new AppDbContext(options);
        
        context.Products.AddRange(new[]
        {
            new Product { Id = 1, Name = "Active Product", IsDeleted = false },
            new Product { Id = 2, Name = "Deleted Product", IsDeleted = true }
        });
        await context.SaveChangesAsync();

        // Act
        var products = await context.Products
            .IgnoreQueryFilters()
            .ToListAsync();

        // Assert
        Assert.That(products.Count, Is.EqualTo(2));
    }
}

9. Advanced Global Filter Patterns

public class AdvancedGlobalFilters
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Time-based filtering
        modelBuilder.Entity<Event>()
            .HasQueryFilter(e => e.StartDate > DateTime.UtcNow);
        
        // Status-based filtering
        modelBuilder.Entity<Job>()
            .HasQueryFilter(j => j.Status != JobStatus.Cancelled);
        
        // Permission-based filtering
        modelBuilder.Entity<File>()
            .HasQueryFilter(f => 
                f.IsPublic || 
                f.OwnerId == _userService.GetCurrentUserId() ||
                f.Permissions.Any(p => p.UserId == _userService.GetCurrentUserId()));
        
        // Hierarchical filtering
        modelBuilder.Entity<Comment>()
            .HasQueryFilter(c => 
                !c.IsDeleted && 
                !c.Post.IsDeleted && 
                c.Post.IsPublished);
    }
}

Key Benefits:

1. Automatic Filtering: No need to remember to add filters to every query
2. Consistency: Ensures all queries follow the same filtering rules
3. Security: Implements row-level security automatically
4. Multi-tenancy: Easy implementation of tenant isolation
5. Soft Deletes: Automatic exclusion of deleted records

Best Practices:

• Keep filters simple and performant
• Use indexed columns in filter conditions
• Test with IgnoreQueryFilters() when needed
• Consider performance impact on complex filters
• Use for security and data isolation, not business logic
• Document global filters for team understanding