In one of our enterprise SaaS projects last year, we discovered a critical bug during a routine audit. Customer A could see order data belonging to Customer B when filtering by a specific date range. The root cause? A missing WHERE TenantId = @tenantId clause in a complex reporting query.
This wasn’t just embarrassing. It was a potential GDPR violation that could have resulted in significant penalties. That incident taught us that manual tenant filtering is prone to human error, especially in large codebases with multiple developers.
EF Core’s global query filters solved this problem by automatically applying tenant isolation at the ORM level. Here’s how we implemented bulletproof tenant isolation that passes SOC2 and HIPAA compliance requirements.
The Multi-Tenant Entity Foundation
Every entity in our system includes a TenantId property. This isn’t optional, it’s the foundation of data isolation.
public class Order
{
public int Id { get; set; }
public Guid TenantId { get; set; }
public string CustomerName { get; set; }
public decimal Amount { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Product
{
public int Id { get; set; }
public Guid TenantId { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
The TenantId is resolved through dependency injection using a tenant provider service:
public interface ITenantProvider
{
Guid TenantId { get; }
}
public class HttpContextTenantProvider : ITenantProvider
{
private readonly IHttpContextAccessor _httpContextAccessor;
public HttpContextTenantProvider(IHttpContextAccessor httpContextAccessor)
{
_httpContextAccessor = httpContextAccessor;
}
public Guid TenantId =>
Guid.Parse(_httpContextAccessor.HttpContext?.User?.FindFirst("tenant_id")?.Value
?? throw new InvalidOperationException("Tenant ID not found"));
}
Implementing Global Query Filters
The magic happens in your DbContext.OnModelCreating method. Global query filters automatically append tenant conditions to all LINQ queries:
public class ApplicationDbContext : DbContext
{
private readonly ITenantProvider _tenantProvider;
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options,
ITenantProvider tenantProvider) : base(options)
{
_tenantProvider = tenantProvider;
}
public DbSet<Order> Orders { get; set; }
public DbSet<Product> Products { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Order>()
.HasQueryFilter(o => o.TenantId == _tenantProvider.TenantId);
modelBuilder.Entity<Product>()
.HasQueryFilter(p => p.TenantId == _tenantProvider.TenantId);
// Create composite indexes for performance
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.TenantId, o.CreatedAt });
modelBuilder.Entity<Product>()
.HasIndex(p => new { p.TenantId, p.Name });
}
}
Now every LINQ query automatically includes the tenant filter:
// This LINQ query...
var recentOrders = await context.Orders
.Where(o => o.CreatedAt > DateTime.UtcNow.AddDays(-30))
.ToListAsync();
// Becomes this SQL automatically:
// SELECT * FROM Orders
// WHERE TenantId = @tenantId AND CreatedAt > @date
SQL Server Performance Considerations
Global query filters generate predictable SQL patterns that SQL Server can optimize effectively:
-- EF Core generates efficient parameterized queries
SELECT [o].[Id], [o].[TenantId], [o].[CustomerName], [o].[Amount], [o].[CreatedAt]
FROM [Orders] AS [o]
WHERE [o].[TenantId] = @__tenantProvider_TenantId_0
AND [o].[CreatedAt] > @__date_1
The key to performance is proper indexing. We create composite indexes with TenantId as the leading column:
// In OnModelCreating
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.TenantId, o.CreatedAt })
.HasDatabaseName("IX_Orders_TenantId_CreatedAt");
Performance Note: In our production environment with 50+ tenants and 2M+ orders, composite indexes reduced query times from 800ms to under 50ms for typical date-range queries.
Critical Pitfalls to Avoid
Global query filters have limitations that can create security vulnerabilities if ignored:
Raw SQL Bypasses Filters
// DANGEROUS: Global filters don't apply to raw SQL
var orders = context.Orders
.FromSqlRaw("SELECT * FROM Orders WHERE Amount > 1000")
.ToList(); // Returns ALL tenants' data!
// SAFE: Always include tenant filter in raw SQL
var orders = context.Orders
.FromSqlRaw("SELECT * FROM Orders WHERE TenantId = {0} AND Amount > 1000",
tenantProvider.TenantId)
.ToList();
Bulk Operations Ignore Filters
// DANGEROUS: Bulk updates bypass global filters
await context.Orders
.Where(o => o.Status == OrderStatus.Pending)
.ExecuteUpdateAsync(o => o.SetProperty(x => x.Status, OrderStatus.Cancelled));
// SAFE: Always include explicit tenant filtering
await context.Orders
.Where(o => o.TenantId == tenantProvider.TenantId && o.Status == OrderStatus.Pending)
.ExecuteUpdateAsync(o => o.SetProperty(x => x.Status, OrderStatus.Cancelled));
Migration and Seeding Operations
Global query filters don’t apply during migrations or data seeding. Always use explicit tenant filtering in these scenarios.
Defense in Depth with SQL Server RLS
For compliance-critical applications, we implement SQL Server Row-Level Security as a backup:
-- Create security predicate function
CREATE FUNCTION dbo.fn_TenantPredicate(@TenantId UNIQUEIDENTIFIER)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS UNIQUEIDENTIFIER);
-- Apply security policy
CREATE SECURITY POLICY TenantIsolationPolicy
ADD FILTER PREDICATE dbo.fn_TenantPredicate(TenantId) ON dbo.Orders,
ADD FILTER PREDICATE dbo.fn_TenantPredicate(TenantId) ON dbo.Products;
Set the session context in your middleware:
public class TenantMiddleware
{
public async Task InvokeAsync(HttpContext context, RequestDelegate next)
{
var tenantId = GetTenantIdFromRequest(context);
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = connection.CreateCommand();
command.CommandText = "EXEC sp_set_session_context N'TenantId', @tenantId";
command.Parameters.AddWithValue("@tenantId", tenantId);
await command.ExecuteNonQueryAsync();
await next(context);
}
}
Production Insight: We’ve found that combining EF Core global filters with SQL Server RLS provides excellent developer experience while maintaining strict security. The EF filters handle 99% of cases automatically, while RLS catches any edge cases or raw SQL queries.
Data Flow Architecture
The complete tenant isolation flow looks like this:
HTTP Request → Authentication Middleware → Tenant Resolution →
DbContext (Global Filters) → SQL Server (RLS + Indexes) → Filtered Results
Each layer provides protection:
- Authentication ensures valid users
- Tenant resolution extracts tenant context
- Global filters auto-apply to LINQ queries
- RLS provides database-level enforcement
- Proper indexes ensure performance
Monitoring and Observability
We monitor tenant isolation through SQL query logging:
public class ApplicationDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging(isDevelopment);
}
}
In production, we use Azure SQL Database’s Query Performance Insight to verify that tenant filters are being applied consistently and performing well.
Conclusion
Global query filters transformed our approach to tenant isolation. What used to require manual filtering in hundreds of queries now happens automatically. Developer errors dropped significantly, and our security posture improved dramatically.
However, global query filters are just one layer of defense. We always combine them with database-level security policies and comprehensive testing. In compliance-heavy industries, this defense-in-depth approach is essential.
The performance impact is minimal when properly indexed, and the security benefits far outweigh any complexity. After implementing this pattern across multiple SaaS products, I can confidently say it’s become our standard approach for multi-tenant data isolation.