EF Core 8 in Production: Multi-Tenancy, Soft Deletes, Auditing, and Query Filters
30 min read
Advanced
A tenant isolation bug in a SaaS application is not just a data leak — it's a compliance incident, a potential contract breach, and a trust event that can end the product. The terrifying part is how easy it is to write. One missing .Where(t => t.TenantId == currentTenant) in a repository method, one background job that forgets to scope its context, and one tenant can read another's data.
EF Core's global query filters exist precisely to make tenant isolation impossible to forget rather than easy to remember. Applied correctly, they turn a correctness requirement into an infrastructure guarantee — the SQL is always right even when the developer doesn't think about it. This tutorial builds a multi-tenant Tasks API that demonstrates every filter pattern, including the places where filters must be deliberately bypassed and how to do that safely.
What You'll Build
A multi-tenant Tasks API (tutorials/ef-core/TasksApiMultiTenant/) with every production EF Core pattern in place:
Tenant resolution from HTTP header and subdomain — ITenantContext scoped per request
Global query filters enforcing tenant isolation and soft-delete at the DbContext level
Soft delete intercepted at SaveChanges — Delete() calls set IsDeleted, they never reach the database as a row removal
Safe filter bypass patterns for admin endpoints, background jobs, and migrations
Composite indexes on tenant columns and a filtered index for soft-delete performance
Compiled queries for the most-called read paths
Integration tests using EF Core in-memory or SQLite that prove tenant A cannot read tenant B's data
Project Setup & Domain Model
Two projects: a Minimal API for the Tasks API, and a separate test project. SQLite powers local development — all patterns translate identically to PostgreSQL or SQL Server.
Terminal
dotnet new webapi -n TasksApiMultiTenant -minimal
cd TasksApiMultiTenant
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
# Test project
dotnet new xunit -n TasksApiMultiTenant.Tests
dotnet add TasksApiMultiTenant.Tests reference TasksApiMultiTenant
dotnet add TasksApiMultiTenant.Tests package Microsoft.EntityFrameworkCore.InMemory
dotnet add TasksApiMultiTenant.Tests package FluentAssertions
Domain Models
Models — Task, Project, TenantMember
// Every entity that belongs to a tenant inherits from TenantAuditableEntity (Section 3)
// These are the concrete domain models
public class TaskItem : TenantAuditableEntity
{
public string Title { get; set; } = "";
public string? Description { get; set; }
public bool IsComplete { get; set; }
public int Priority { get; set; } // 1=Low, 2=Medium, 3=High
public DateTime? DueDate { get; set; }
public int? ProjectId { get; set; }
// Navigation
public Project? Project { get; set; }
}
public class Project : TenantAuditableEntity
{
public string Name { get; set; } = "";
public string? Description { get; set; }
public bool IsArchived { get; set; }
// Navigation
public ICollection<TaskItem> Tasks { get; set; } = [];
}
public class TenantMember : TenantAuditableEntity
{
public string UserId { get; set; } = "";
public string Email { get; set; } = "";
public string Role { get; set; } = "Member"; // Member, Admin, Owner
public bool IsActive { get; set; } = true;
}
Model Everything as Soft-Deletable by Default
In a multi-tenant SaaS application, permanent deletion creates audit gaps — tenants lose the ability to see what was deleted, by whom, and when. Default every entity to soft-deletable via the base class (Section 3). Use hard deletes only for compliance-driven data erasure (GDPR right-to-erasure) and implement those as separate, explicit data purge operations rather than normal delete calls.
Resolving the Current Tenant
Before EF Core can filter by tenant, something must determine which tenant the current request belongs to. Two common strategies: an X-Tenant-Id HTTP header (simple, good for API clients) and subdomain extraction (good for user-facing apps). The resolution result flows into a scoped ITenantContext that the DbContext reads.
ITenantContext and Implementations
Tenancy/ITenantContext.cs
// The contract the DbContext and all services use — never touches HTTP directly
public interface ITenantContext
{
string TenantId { get; }
bool HasTenant { get; }
}
// HTTP request implementation — scoped per request
public class HttpTenantContext : ITenantContext
{
public string TenantId { get; }
public bool HasTenant { get; }
public HttpTenantContext(IHttpContextAccessor accessor)
{
var ctx = accessor.HttpContext;
if (ctx is null) { HasTenant = false; TenantId = ""; return; }
// Strategy 1: X-Tenant-Id header
var headerId = ctx.Request.Headers["X-Tenant-Id"].FirstOrDefault();
if (!string.IsNullOrWhiteSpace(headerId))
{
TenantId = headerId.Trim().ToLowerInvariant();
HasTenant = true;
return;
}
// Strategy 2: subdomain (e.g., acme.yoursaas.com → tenantId = "acme")
var host = ctx.Request.Host.Host;
var parts = host.Split('.');
if (parts.Length >= 3) // subdomain.domain.tld
{
TenantId = parts[0].ToLowerInvariant();
HasTenant = true;
return;
}
HasTenant = false;
TenantId = "";
}
}
// Background job implementation — tenant set explicitly from job payload
public class ExplicitTenantContext(string tenantId) : ITenantContext
{
public string TenantId { get; } = tenantId;
public bool HasTenant { get; } = !string.IsNullOrEmpty(tenantId);
}
// System/admin context — no tenant filtering (use with extreme care)
public class SystemTenantContext : ITenantContext
{
public string TenantId => "";
public bool HasTenant => false;
}
Tenant Validation Middleware
Middleware/TenantResolutionMiddleware.cs
public class TenantResolutionMiddleware(RequestDelegate next)
{
// These paths don't require a tenant — health checks, auth, swagger
private static readonly HashSet<string> TenantExemptPaths =
[
"/health/live", "/health/ready",
"/swagger", "/swagger/v1/swagger.json",
"/auth/login", "/auth/register"
];
public async Task InvokeAsync(HttpContext ctx, ITenantContext tenantCtx)
{
// Skip tenant check for exempt paths
if (TenantExemptPaths.Any(p =>
ctx.Request.Path.StartsWithSegments(p, StringComparison.OrdinalIgnoreCase)))
{
await next(ctx);
return;
}
// Reject requests with no resolvable tenant
if (!tenantCtx.HasTenant)
{
ctx.Response.StatusCode = 400;
await ctx.Response.WriteAsJsonAsync(new
{
type = "https://tools.ietf.org/html/rfc7807",
title = "Tenant Required",
status = 400,
detail = "This API requires a tenant identifier. " +
"Provide X-Tenant-Id header or use a tenant subdomain."
});
return;
}
await next(ctx);
}
}
// Registration in Program.cs
builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ITenantContext, HttpTenantContext>();
// ...
app.UseMiddleware<TenantResolutionMiddleware>();
Validate Tenant Existence Separately from Resolution
The middleware above checks that a tenant ID was provided — not that it's a valid tenant. Add a second check that looks up the tenant ID against your tenants table (cached in IMemoryCache with a short TTL). Return 404 for unknown tenant IDs, not 400 — returning 400 confirms that the format was valid, which is information disclosure. An unknown tenant should be indistinguishable from a malformed request.
Base Entity with Audit & Soft-Delete Fields
A single abstract base class gives every entity its tenant column, soft-delete flag, and full audit trail — six fields that are always populated automatically and never touched by application code directly.
Entities/TenantAuditableEntity.cs
/// <summary>
/// Base class for all tenant-scoped, soft-deletable, auditable entities.
/// Fields are populated automatically in SaveChangesAsync — never set them manually.
/// </summary>
public abstract class TenantAuditableEntity
{
public int Id { get; set; }
// ─── Tenant isolation ────────────────────────────────────────────
public string TenantId { get; set; } = ""; // set by DbContext.SaveChangesAsync
// ─── Soft delete ─────────────────────────────────────────────────
public bool IsDeleted { get; set; }
public DateTime? DeletedAt { get; set; }
public string? DeletedBy { get; set; }
// ─── Audit trail ─────────────────────────────────────────────────
public DateTime CreatedAt { get; set; }
public string CreatedBy { get; set; } = "";
public DateTime UpdatedAt { get; set; }
public string UpdatedBy { get; set; } = "";
}
// Interfaces for entities that need finer-grained control
// (optional — use if you want some entities without soft delete)
public interface ISoftDeletable
{
bool IsDeleted { get; set; }
DateTime? DeletedAt { get; set; }
string? DeletedBy { get; set; }
}
public interface IAuditable
{
DateTime CreatedAt { get; set; }
string CreatedBy { get; set; }
DateTime UpdatedAt { get; set; }
string UpdatedBy { get; set; }
}
public interface ITenantScoped
{
string TenantId { get; set; }
}
Why init Setters Would Be Wrong Here
Audit and tenant fields must be set by the SaveChangesAsync override after EF Core tracks the entity — not at construction time. Using init setters would prevent the DbContext from updating them after creation. Use regular set accessors, but mark them internal set if you want to prevent application code in other assemblies from setting them directly. The DbContext assembly can still write them; external callers cannot.
DbContext: Registering Global Query Filters
Global query filters in EF Core attach a WHERE condition to every LINQ query against a given entity type — automatically, without any action from the caller. Register them in OnModelCreating against all entities that inherit from TenantAuditableEntity.
Data/TasksDbContext.cs — OnModelCreating with Filters
public class TasksDbContext(
DbContextOptions<TasksDbContext> options,
ITenantContext tenantContext,
ICurrentUserContext currentUser)
: DbContext(options)
{
public DbSet<TaskItem> Tasks => Set<TaskItem>();
public DbSet<Project> Projects => Set<Project>();
public DbSet<TenantMember> Members => Set<TenantMember>();
protected override void OnModelCreating(ModelBuilder mb)
{
base.OnModelCreating(mb);
// Apply filters to every entity that is a TenantAuditableEntity
// Uses reflection to find all such entity types registered in the model
foreach (var entityType in mb.Model.GetEntityTypes())
{
if (!typeof(TenantAuditableEntity).IsAssignableFrom(entityType.ClrType))
continue;
ApplyTenantAndSoftDeleteFilters(mb, entityType.ClrType);
}
// Table configuration and indexes (Section 7)
mb.Entity<TaskItem>(entity =>
{
entity.ToTable("Tasks");
// Composite index: tenant + soft-delete (the most common filter combination)
entity.HasIndex(t => new { t.TenantId, t.IsDeleted })
.HasDatabaseName("IX_Tasks_TenantId_IsDeleted");
// Filtered index: active tasks only — avoids bloat from deleted rows
entity.HasIndex(t => new { t.TenantId, t.IsComplete, t.Priority })
.HasFilter("[IsDeleted] = 0")
.HasDatabaseName("IX_Tasks_TenantId_Active");
entity.Property(t => t.Title).HasMaxLength(500).IsRequired();
entity.Property(t => t.TenantId).HasMaxLength(100).IsRequired();
});
mb.Entity<Project>(entity =>
{
entity.ToTable("Projects");
entity.HasIndex(p => new { p.TenantId, p.IsDeleted })
.HasDatabaseName("IX_Projects_TenantId_IsDeleted");
entity.Property(p => p.Name).HasMaxLength(200).IsRequired();
});
mb.Entity<TenantMember>(entity =>
{
entity.ToTable("TenantMembers");
entity.HasIndex(m => new { m.TenantId, m.UserId })
.IsUnique()
.HasFilter("[IsDeleted] = 0") // allow re-add after soft delete
.HasDatabaseName("IX_TenantMembers_TenantId_UserId_Unique");
});
}
// Builds and applies the combined tenant + soft-delete filter expression
private static void ApplyTenantAndSoftDeleteFilters(
ModelBuilder mb, Type entityType)
{
// Build: e => !e.IsDeleted && (e.TenantId == tenantContext.TenantId || !tenantContext.HasTenant)
// Using expression trees for compile-time correctness
var mb2 = mb.Entity(entityType);
var param = Expression.Parameter(entityType, "e");
// !e.IsDeleted
var isDeletedProp = Expression.Property(param, nameof(TenantAuditableEntity.IsDeleted));
var notDeleted = Expression.Not(isDeletedProp);
// e.TenantId == tenantContext.TenantId
var tenantIdProp = Expression.Property(param, nameof(TenantAuditableEntity.TenantId));
var tenantIdValue = Expression.Property(
Expression.Constant(mb2.Metadata.Model), // captured in closure
"TenantIdForFilter"); // We use the field approach below instead
// See complete implementation in Section 11 (end-to-end DbContext)
// The filter references _tenantContext directly via closure
}
}
Practical Filter Registration Pattern
Expression tree construction is verbose. A cleaner approach uses a helper method on the entity type itself, or a generic configuration extension:
Cleaner Filter Registration via Generic Helper
// Extension method — hides the expression tree ceremony
public static class ModelBuilderExtensions
{
public static void ApplyTenantSoftDeleteFilter<TEntity>(
this ModelBuilder mb,
ITenantContext tenantCtx)
where TEntity : TenantAuditableEntity
{
mb.Entity<TEntity>().HasQueryFilter(e =>
!e.IsDeleted &&
(e.TenantId == tenantCtx.TenantId || !tenantCtx.HasTenant));
}
}
// OnModelCreating — clean and readable
protected override void OnModelCreating(ModelBuilder mb)
{
mb.ApplyTenantSoftDeleteFilter<TaskItem>(tenantContext);
mb.ApplyTenantSoftDeleteFilter<Project>(tenantContext);
mb.ApplyTenantSoftDeleteFilter<TenantMember>(tenantContext);
// OR — apply to all entity types automatically
foreach (var type in mb.Model.GetEntityTypes()
.Where(t => typeof(TenantAuditableEntity).IsAssignableFrom(t.ClrType)))
{
// Invoke the generic method for each entity type via reflection
typeof(ModelBuilderExtensions)
.GetMethod(nameof(ApplyTenantSoftDeleteFilter))!
.MakeGenericMethod(type.ClrType)
.Invoke(null, [mb, tenantContext]);
}
}
EF Core Captures the Filter Expression at Model Build Time
The filter lambda e => !e.IsDeleted && e.TenantId == tenantCtx.TenantId captures tenantCtx as a closure reference — not a value snapshot. This means each query re-evaluates tenantCtx.TenantId at runtime, which is exactly what you want for a scoped ITenantContext. If you accidentally pass a hard-coded string instead of the context reference, the filter bakes the tenant ID into the model and every query uses that same ID regardless of the current request. Always capture the context object, never its current property value.
Overriding SaveChangesAsync is where soft delete and audit fields become invisible to application code. Deletes are intercepted and converted to updates. New and modified entities get their timestamps and user stamps written automatically. Application code never touches these fields directly.
TasksDbContext — SaveChangesAsync Override
public override async Task<int> SaveChangesAsync(
CancellationToken cancellationToken = default)
{
var now = DateTime.UtcNow;
var userId = currentUser.UserId ?? "system";
var tenantId = tenantContext.TenantId;
// Process all tracked entity changes
foreach (var entry in ChangeTracker.Entries<TenantAuditableEntity>())
{
switch (entry.State)
{
case EntityState.Added:
// Stamp tenant, creation time, and creator
entry.Entity.TenantId = tenantId;
entry.Entity.CreatedAt = now;
entry.Entity.CreatedBy = userId;
entry.Entity.UpdatedAt = now;
entry.Entity.UpdatedBy = userId;
break;
case EntityState.Modified:
// Never allow tenant ID to be changed after creation
entry.Property(e => e.TenantId).IsModified = false;
entry.Property(e => e.CreatedAt).IsModified = false;
entry.Property(e => e.CreatedBy).IsModified = false;
// Stamp last-modified
entry.Entity.UpdatedAt = now;
entry.Entity.UpdatedBy = userId;
break;
case EntityState.Deleted:
// INTERCEPT: convert hard delete into soft delete
entry.State = EntityState.Modified; // change tracking state
entry.Entity.IsDeleted = true;
entry.Entity.DeletedAt = now;
entry.Entity.DeletedBy = userId;
// Freeze audit fields — don't update UpdatedAt on soft delete
entry.Property(e => e.UpdatedAt).IsModified = false;
entry.Property(e => e.UpdatedBy).IsModified = false;
break;
}
}
return await base.SaveChangesAsync(cancellationToken);
}
// Security: prevent TenantId tampering via the public model binder
// If a PUT request includes TenantId in the JSON body, ignore it
// The IsModified = false line above handles this for Modified entities
// For Added entities, always overwrite with the context's tenant ID
ICurrentUserContext
Auth/ICurrentUserContext.cs
public interface ICurrentUserContext
{
string? UserId { get; }
string? Email { get; }
bool IsAdmin { get; }
}
// HTTP implementation — reads from ClaimsPrincipal
public class HttpCurrentUserContext(IHttpContextAccessor accessor) : ICurrentUserContext
{
private ClaimsPrincipal? User =>
accessor.HttpContext?.User;
public string? UserId => User?.FindFirstValue(ClaimTypes.NameIdentifier);
public string? Email => User?.FindFirstValue(ClaimTypes.Email);
public bool IsAdmin => User?.IsInRole("Admin") ?? false;
}
// Registration in Program.cs
builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ICurrentUserContext, HttpCurrentUserContext>();
builder.Services.AddScoped<ITenantContext, HttpTenantContext>();
// DbContext registration — scoped so it gets fresh ITenantContext per request
builder.Services.AddDbContext<TasksDbContext>(opt =>
opt.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));
// DbContext constructor receives ITenantContext and ICurrentUserContext via DI
Bypassing Filters Safely: Admin, Jobs & Migrations
Three legitimate bypass scenarios exist. Each requires deliberate action — not a simple boolean flag — and each should be auditable.
Admin Bypass — Explicit IgnoreQueryFilters
Admin Repository Pattern
public class AdminTaskRepository(TasksDbContext db)
{
// Admin-only: returns ALL tasks across ALL tenants including deleted
// Named explicitly to make the bypass intent visible at the call site
public async Task<IReadOnlyList<TaskItem>> GetAllTenantsIncludingDeletedAsync(
CancellationToken ct = default) =>
await db.Tasks
.IgnoreQueryFilters() // bypass tenant AND soft-delete filters
.ToListAsync(ct);
// Admin: view deleted items for a specific tenant (soft-delete bypass only)
// Re-adds the tenant filter manually after bypassing
public async Task<IReadOnlyList<TaskItem>> GetDeletedForTenantAsync(
string tenantId,
CancellationToken ct = default) =>
await db.Tasks
.IgnoreQueryFilters() // bypasses BOTH filters...
.Where(t => t.TenantId == tenantId && t.IsDeleted) // ...so re-add tenant manually
.ToListAsync(ct);
// Admin: permanently hard-delete a soft-deleted record (GDPR erasure)
public async Task HardDeleteAsync(int id, string tenantId, CancellationToken ct = default)
{
var task = await db.Tasks
.IgnoreQueryFilters()
.FirstOrDefaultAsync(t => t.Id == id && t.TenantId == tenantId, ct);
if (task is null) return;
db.Tasks.Remove(task); // this will NOT be intercepted as soft-delete
// because we call Remove() on a non-soft-deleted entity
// after manually constructing the delete path
await db.SaveChangesAsync(ct);
}
}
Background Job Bypass — Explicit Tenant Context
Background Job with Explicit Tenant Scoping
// Job payload carries the tenant ID explicitly
public record SendTaskRemindersJob(string TenantId, DateTime DueDate);
public class TaskReminderJobHandler(IServiceScopeFactory scopeFactory)
{
public async Task HandleAsync(SendTaskRemindersJob job, CancellationToken ct)
{
// Create a new scope — never reuse an HTTP-scoped DbContext in a job
await using var scope = scopeFactory.CreateAsyncScope();
// Register an explicit tenant context for this scope
// ExplicitTenantContext sets TenantId from the job payload
var explicitTenantCtx = new ExplicitTenantContext(job.TenantId);
// The DbContext received here will have the job's tenant set
// because we override the scoped ITenantContext registration
var db = scope.ServiceProvider.GetRequiredService<TasksDbContext>();
// Queries are automatically scoped to job.TenantId via the filter
var overdueTasks = await db.Tasks
.Where(t => !t.IsComplete && t.DueDate <= job.DueDate)
.ToListAsync(ct);
// Process reminders for this tenant's tasks...
}
}
// For jobs that need cross-tenant access, use IgnoreQueryFilters() deliberately:
// EVERY SUCH METHOD must have a code comment explaining why cross-tenant access is needed
// and must be reviewed in pull requests
var allTenantsOverdue = await db.Tasks
.IgnoreQueryFilters()
// INTENTIONAL: cross-tenant aggregation for platform health dashboard
// Reviewed and approved: PR #847
.Where(t => !t.IsDeleted && !t.IsComplete && t.DueDate <= DateTime.UtcNow)
.GroupBy(t => t.TenantId)
.Select(g => new { TenantId = g.Key, Count = g.Count() })
.ToListAsync(ct);
IgnoreQueryFilters() Bypasses ALL Active Filters Simultaneously
There is no EF Core API to selectively bypass a single filter while keeping others active. IgnoreQueryFilters() on a query removes both the tenant filter and the soft-delete filter at once. Every call site that uses it must manually re-add whichever filters it still needs as explicit .Where() clauses. This is not optional — forgetting to re-add the tenant condition after an IgnoreQueryFilters() call is a data isolation bug waiting to happen.
Indexes & Query Performance
Every query against a multi-tenant table implicitly filters on TenantId. Without an index starting with TenantId, every query is a full table scan. At small scale this is invisible. At production scale with millions of rows, it's catastrophic.
Essential Index Strategy
Index Configuration in OnModelCreating
mb.Entity<TaskItem>(entity =>
{
// 1. Primary tenant filter index — used by almost every query
// Covers: WHERE TenantId = @tid AND IsDeleted = 0
entity.HasIndex(t => new { t.TenantId, t.IsDeleted })
.HasDatabaseName("IX_Tasks_TenantId_IsDeleted");
// 2. Active task listing — covers ORDER BY and filter patterns
// Filtered index excludes deleted rows = smaller index, faster seeks
entity.HasIndex(t => new { t.TenantId, t.IsComplete, t.Priority, t.DueDate })
.HasFilter("[IsDeleted] = 0") // SQL Server / SQLite syntax
// For PostgreSQL: .HasFilter("\"IsDeleted\" = false")
.HasDatabaseName("IX_Tasks_TenantId_Active_Priority");
// 3. Project relationship — JOIN path is tenant-scoped
entity.HasIndex(t => new { t.TenantId, t.ProjectId, t.IsDeleted })
.HasDatabaseName("IX_Tasks_TenantId_ProjectId");
// 4. Audit trail queries — admin looking up what changed
entity.HasIndex(t => new { t.TenantId, t.UpdatedAt })
.HasDatabaseName("IX_Tasks_TenantId_UpdatedAt");
});
mb.Entity<Project>(entity =>
{
entity.HasIndex(p => new { p.TenantId, p.IsDeleted, p.IsArchived })
.HasDatabaseName("IX_Projects_TenantId_Active");
});
mb.Entity<TenantMember>(entity =>
{
// Unique active membership — prevents duplicate members
entity.HasIndex(m => new { m.TenantId, m.UserId })
.IsUnique()
.HasFilter("[IsDeleted] = 0")
.HasDatabaseName("IX_TenantMembers_Unique_Active");
entity.HasIndex(m => new { m.TenantId, m.Email })
.HasDatabaseName("IX_TenantMembers_TenantId_Email");
});
Analysing Generated SQL
Log Generated SQL in Development
// appsettings.Development.json — verbose EF Core SQL logging
{
"Logging": {
"LogLevel": {
"Microsoft.EntityFrameworkCore.Database.Command": "Information"
}
}
}
// Or in Program.cs for structured parameter logging
builder.Services.AddDbContext<TasksDbContext>(opt =>
{
opt.UseSqlite(connectionString);
if (builder.Environment.IsDevelopment())
{
opt.EnableSensitiveDataLogging(); // logs parameter values
opt.EnableDetailedErrors(); // verbose exception messages
opt.LogTo(Console.WriteLine,
[DbLoggerCategory.Database.Command.Name],
LogLevel.Information);
}
});
// Expected SQL for: db.Tasks.Where(t => t.Priority == 3).ToListAsync()
// SELECT t."Id", t."Title", ...
// FROM "Tasks" AS t
// WHERE t."IsDeleted" = 0 <-- soft-delete filter (automatic)
// AND t."TenantId" = @tid <-- tenant filter (automatic)
// AND t."Priority" = 3 <-- explicit application condition
Benchmark Your Filter Indexes Before Going to Production
Use EXPLAIN ANALYZE (PostgreSQL) or SET STATISTICS IO ON + execution plans (SQL Server) against a dataset representative of your production volume — at least 500K rows per tenant table, with 10+ tenants. A query that takes 2ms on 1,000 rows takes 200ms on 100,000 rows without an index — and your tenant filter will always be in the query, making its index non-optional. Add the benchmarks to your CI pipeline using BenchmarkDotNet so a new migration that removes or changes an index is caught before deploy.
Compiled Queries for Hot Paths
EF Core translates LINQ to SQL each time a query runs — model validation, expression tree traversal, SQL generation. For endpoints called thousands of times per second, compiling queries once at startup eliminates that overhead on every subsequent call.
Queries/CompiledQueries.cs
public static class CompiledQueries
{
// Compiled query: list tasks for a tenant (filters applied automatically)
// Parameters after DbContext: additional query parameters
public static readonly Func<TasksDbContext, bool?, int, int, IAsyncEnumerable<TaskItem>>
GetTasksPage = EF.CompileAsyncQuery(
(TasksDbContext db, bool? isComplete, int skip, int take) =>
db.Tasks
.Where(t => isComplete == null || t.IsComplete == isComplete)
.OrderBy(t => t.Priority)
.ThenBy(t => t.DueDate)
.Skip(skip)
.Take(take));
// Compiled query: get a single task by ID (tenant filter applied automatically)
public static readonly Func<TasksDbContext, int, Task<TaskItem?>>
GetTaskById = EF.CompileAsyncQuery(
(TasksDbContext db, int id) =>
db.Tasks
.Include(t => t.Project)
.FirstOrDefault(t => t.Id == id));
// Compiled query: count tasks by priority for dashboard
public static readonly Func<TasksDbContext, IAsyncEnumerable<PriorityCount>>
GetTaskCountsByPriority = EF.CompileAsyncQuery(
(TasksDbContext db) =>
db.Tasks
.Where(t => !t.IsComplete)
.GroupBy(t => t.Priority)
.Select(g => new PriorityCount(g.Key, g.Count())));
}
public record PriorityCount(int Priority, int Count);
// Usage in endpoint handlers
app.MapGet("/tasks", async (
TasksDbContext db,
bool? isComplete,
int page = 1,
int size = 20) =>
{
var skip = (page - 1) * size;
var tasks = new List<TaskItem>();
// Compiled query — zero LINQ translation overhead on repeated calls
await foreach (var task in CompiledQueries.GetTasksPage(db, isComplete, skip, size))
tasks.Add(task);
return TypedResults.Ok(tasks);
});
Global Query Filters Apply to Compiled Queries Too
Compiled queries capture the LINQ expression tree, not the generated SQL. When the compiled query runs, EF Core still applies the current global query filters to the expression before translation. This means GetTasksPage will always include the tenant and soft-delete conditions in its generated SQL, even though those conditions are not written in the compiled query definition. No special handling needed — filters work exactly as they do for regular LINQ queries.
Repository Pattern & API Endpoints
A thin repository layer over EF Core keeps endpoint handlers clean and centralises the places where IgnoreQueryFilters might legitimately appear.
Repositories/TaskRepository.cs
public class TaskRepository(TasksDbContext db)
{
// Normal reads — tenant and soft-delete filters applied automatically
public async Task<IReadOnlyList<TaskItem>> GetPageAsync(
bool? isComplete, int page, int size, CancellationToken ct = default)
{
var tasks = new List<TaskItem>();
await foreach (var t in CompiledQueries.GetTasksPage(
db, isComplete, (page - 1) * size, size))
tasks.Add(t);
return tasks;
}
public async Task<TaskItem?> GetByIdAsync(int id, CancellationToken ct = default) =>
await CompiledQueries.GetTaskById(db, id);
public async Task<TaskItem> CreateAsync(TaskItem task, CancellationToken ct = default)
{
db.Tasks.Add(task);
await db.SaveChangesAsync(ct); // SaveChanges sets TenantId, CreatedAt, CreatedBy
return task;
}
public async Task<TaskItem?> UpdateAsync(
int id, Action<TaskItem> applyChanges, CancellationToken ct = default)
{
var task = await GetByIdAsync(id, ct);
if (task is null) return null;
applyChanges(task);
await db.SaveChangesAsync(ct); // SaveChanges sets UpdatedAt, UpdatedBy
return task;
}
public async Task<bool> SoftDeleteAsync(int id, CancellationToken ct = default)
{
var task = await GetByIdAsync(id, ct);
if (task is null) return false;
db.Tasks.Remove(task); // intercepted in SaveChangesAsync as soft delete
await db.SaveChangesAsync(ct);
return true;
}
}
// API endpoint handlers — thin, readable, no EF concerns
app.MapGet("/tasks/{id:int}", async (int id, TaskRepository repo) =>
{
var task = await repo.GetByIdAsync(id);
return task is not null ? TypedResults.Ok(task) : TypedResults.NotFound();
})
.WithTags("Tasks");
app.MapPost("/tasks", async (CreateTaskRequest req, TaskRepository repo) =>
{
var task = new TaskItem
{
Title = req.Title,
Description = req.Description,
Priority = req.Priority,
DueDate = req.DueDate,
ProjectId = req.ProjectId
// TenantId, CreatedAt, CreatedBy set by SaveChangesAsync
};
var created = await repo.CreateAsync(task);
return TypedResults.Created($"/tasks/{created.Id}", created);
})
.WithTags("Tasks");
app.MapDelete("/tasks/{id:int}", async (int id, TaskRepository repo) =>
{
var deleted = await repo.SoftDeleteAsync(id);
return deleted ? TypedResults.NoContent() : TypedResults.NotFound();
})
.WithTags("Tasks");
record CreateTaskRequest(
string Title,
string? Description,
int Priority,
DateTime? DueDate,
int? ProjectId);
Integration Tests That Prove Tenant Isolation
Unit tests can verify logic. Only integration tests that exercise the actual DbContext with real query filters can prove that tenant A cannot read tenant B's data. These tests are the compliance evidence for your multi-tenancy guarantee.
Tests/TenantIsolationTests.cs
public class TenantIsolationTests : IAsyncLifetime
{
private TasksDbContext _db = null!;
// Seeds both tenants' data before each test
public async Task InitializeAsync()
{
_db = CreateDbContext("tenant-alpha"); // default viewing tenant
await _db.Database.EnsureCreatedAsync();
// Seed tenant-alpha tasks (using alpha's context)
await SeedTasksForTenantAsync("tenant-alpha", 5);
// Seed tenant-beta tasks (using beta's context — different context instance)
await using var betaDb = CreateDbContext("tenant-beta");
await SeedTasksForTenantAsync("tenant-beta", 3, betaDb);
}
[Fact]
public async Task GetTasks_OnlyReturnsTenantAlphaTasks()
{
// Act — db is scoped to tenant-alpha
var tasks = await _db.Tasks.ToListAsync();
// Assert
tasks.Should().HaveCount(5);
tasks.Should().AllSatisfy(t =>
t.TenantId.Should().Be("tenant-alpha",
because: "global query filter must prevent cross-tenant data access"));
}
[Fact]
public async Task GetTasks_TenantBetaCannotSeeTenantAlphaTasks()
{
// Arrange — create a context for tenant-beta
await using var betaDb = CreateDbContext("tenant-beta");
// Act
var betaTasks = await betaDb.Tasks.ToListAsync();
// Assert
betaTasks.Should().HaveCount(3,
because: "tenant-beta should only see its own 3 tasks");
betaTasks.Should().AllSatisfy(t =>
t.TenantId.Should().Be("tenant-beta"));
}
[Fact]
public async Task SoftDelete_TaskNotVisibleInNormalQuery_ButVisibleToAdmin()
{
// Arrange — get first task and soft-delete it
var task = await _db.Tasks.FirstAsync();
_db.Tasks.Remove(task);
await _db.SaveChangesAsync();
// Act — normal query (soft-delete filter active)
var visible = await _db.Tasks.ToListAsync();
visible.Should().HaveCount(4, because: "deleted task should be filtered out");
// Admin query — bypasses filters
var withDeleted = await _db.Tasks
.IgnoreQueryFilters()
.Where(t => t.TenantId == "tenant-alpha")
.ToListAsync();
withDeleted.Should().HaveCount(5, because: "admin sees all including deleted");
// Verify soft-delete fields were set
var deletedTask = withDeleted.Single(t => t.Id == task.Id);
deletedTask.IsDeleted.Should().BeTrue();
deletedTask.DeletedAt.Should().BeCloseTo(DateTime.UtcNow, TimeSpan.FromSeconds(2));
deletedTask.DeletedBy.Should().Be("test-user");
}
[Fact]
public async Task CreateTask_TenantIdSetAutomatically()
{
// Act — no TenantId set on the entity
var newTask = new TaskItem { Title = "Auto-tenant test", Priority = 1 };
_db.Tasks.Add(newTask);
await _db.SaveChangesAsync();
// Assert — TenantId was set by SaveChangesAsync
newTask.TenantId.Should().Be("tenant-alpha");
newTask.CreatedAt.Should().BeCloseTo(DateTime.UtcNow, TimeSpan.FromSeconds(2));
newTask.CreatedBy.Should().Be("test-user");
}
[Fact]
public async Task UpdateTask_TenantIdCannotBeChanged()
{
var task = await _db.Tasks.FirstAsync();
var originalTenantId = task.TenantId;
// Attempt to change TenantId
task.TenantId = "tenant-beta"; // should be ignored by SaveChanges
await _db.SaveChangesAsync();
// Re-fetch and verify
_db.ChangeTracker.Clear(); // clear cache to force re-read
var reloaded = await _db.Tasks.IgnoreQueryFilters()
.FirstAsync(t => t.Id == task.Id);
reloaded.TenantId.Should().Be(originalTenantId,
because: "TenantId must be immutable after creation");
}
public async Task DisposeAsync() => await _db.DisposeAsync();
// ─── Test helpers ─────────────────────────────────────────────────
private static TasksDbContext CreateDbContext(string tenantId)
{
var options = new DbContextOptionsBuilder<TasksDbContext>()
.UseInMemoryDatabase($"TasksDb-{Guid.NewGuid()}")
.Options;
var tenantCtx = new ExplicitTenantContext(tenantId);
var userCtx = new TestUserContext("test-user");
return new TasksDbContext(options, tenantCtx, userCtx);
}
private static async Task SeedTasksForTenantAsync(
string tenantId, int count, TasksDbContext? ctx = null)
{
var useCtx = ctx ?? CreateDbContext(tenantId);
var ownCtx = ctx is null;
for (var i = 1; i <= count; i++)
{
useCtx.Tasks.Add(new TaskItem
{
Title = $"{tenantId} Task {i}",
Priority = i % 3 + 1
});
}
await useCtx.SaveChangesAsync();
if (ownCtx) await useCtx.DisposeAsync();
}
private class TestUserContext(string userId) : ICurrentUserContext
{
public string? UserId => userId;
public string? Email => $"{userId}@test.com";
public bool IsAdmin => false;
}
}
Run Isolation Tests Against a Real Database, Not Just In-Memory
In-memory databases skip query filter mechanics in some edge cases. Run your tenant isolation test suite against SQLite (with UseInMemoryDatabase replaced by UseSqlite("Data Source=:memory:")) to verify that generated SQL actually includes the filter predicates. For the highest confidence, mirror your CI environment by running the isolation suite against PostgreSQL or SQL Server using Testcontainers — the only way to be certain that your index-backed filters work under realistic execution plans.
End-to-End: Complete DbContext & Program.cs
All layers assembled — complete TasksDbContext with filters, SaveChangesAsync override, and a Program.cs that wires everything together in the correct order.
var builder = WebApplication.CreateBuilder(args);
// ─── Core services ────────────────────────────────────────────────────────
builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ITenantContext, HttpTenantContext>();
builder.Services.AddScoped<ICurrentUserContext, HttpCurrentUserContext>();
builder.Services.AddDbContext<TasksDbContext>(opt =>
opt.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")
?? "Data Source=tasks.db"));
builder.Services.AddScoped<TaskRepository>();
builder.Services.AddScoped<AdminTaskRepository>();
// Authentication & authorization (brief — see API Security tutorial for full setup)
builder.Services.AddAuthentication().AddJwtBearer();
builder.Services.AddAuthorization(opt =>
{
opt.AddPolicy("AdminOnly", p => p.RequireRole("Admin"));
opt.FallbackPolicy = new AuthorizationPolicyBuilder()
.RequireAuthenticatedUser().Build();
});
var app = builder.Build();
// ─── Middleware ───────────────────────────────────────────────────────────
app.UseHttpsRedirection();
app.UseAuthentication();
app.UseAuthorization();
app.UseMiddleware<TenantResolutionMiddleware>();
// ─── Migrations on startup (development only) ────────────────────────────
if (app.Environment.IsDevelopment())
{
using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<TasksDbContext>();
await db.Database.MigrateAsync();
}
// ─── API endpoints ────────────────────────────────────────────────────────
var tasks = app.MapGroup("/tasks").RequireAuthorization();
tasks.MapGet("/", async (TaskRepository r, bool? isComplete, int page = 1) =>
TypedResults.Ok(await r.GetPageAsync(isComplete, page, 20)));
tasks.MapGet("/{id}", async (int id, TaskRepository r) =>
{
var t = await r.GetByIdAsync(id);
return t is not null ? TypedResults.Ok(t) : TypedResults.NotFound();
});
tasks.MapPost("/", async (CreateTaskRequest req, TaskRepository r) =>
{
var created = await r.CreateAsync(new TaskItem
{
Title = req.Title, Description = req.Description,
Priority = req.Priority, DueDate = req.DueDate, ProjectId = req.ProjectId
});
return TypedResults.Created($"/tasks/{created.Id}", created);
});
tasks.MapDelete("/{id}", async (int id, TaskRepository r) =>
{
var ok = await r.SoftDeleteAsync(id);
return ok ? TypedResults.NoContent() : TypedResults.NotFound();
});
// Admin endpoints — bypass tenant isolation
app.MapGet("/admin/tasks",
async (AdminTaskRepository r) =>
TypedResults.Ok(await r.GetAllTenantsIncludingDeletedAsync()))
.RequireAuthorization("AdminOnly");
app.Run();
record CreateTaskRequest(string Title, string? Description, int Priority,
DateTime? DueDate, int? ProjectId);
Common Pitfalls & How to Avoid Them
The patterns in this tutorial prevent the most common mistakes. But there are a few subtler traps that catch even experienced EF Core developers in multi-tenant systems.
❌ Reusing a DbContext Across Tenant Boundaries
A DbContext is scoped to a single HTTP request. Never inject a singleton DbContext — the tenant filter closure captures the first request's tenant ID and all subsequent requests see the wrong data. Always register AddDbContext<T> as scoped (the default).
❌ Calling IgnoreQueryFilters on a Non-Admin Code Path
A general-purpose repository method that accepts a bool ignoreFilters = false parameter is a security trap. Any caller can pass true without understanding they're also bypassing the tenant filter. Use explicitly named admin methods instead.
❌ Missing the Tenant Filter on Navigation Property Queries
Global filters apply to the root entity type. If you access a navigation property on a loaded entity — task.Project.Members — and Project or Members are not filtered themselves, you can traverse to data from other tenants via eager or explicit loading. Ensure every navigable entity in your model has a global query filter.
❌ Using Hard-Coded Strings for TenantId in Seeds/Tests
Seeding test data with TenantId = "test" and then testing with a context that has tenantContext.TenantId = "tenant-alpha" will return zero results — not because isolation works, but because the IDs don't match. Always seed data through a context that has the same tenant ID you'll query with.
Further Reading & Next Steps
The Tasks API now has robust multi-tenant data isolation, automatic audit trails, and integration tests that prove the guarantees hold. These patterns are production-tested across many SaaS .NET applications and adapt directly to PostgreSQL, SQL Server, and Azure SQL.
Add database-level enforcement as a second layer of defence: PostgreSQL Row-Level Security policies enforce tenant isolation in the database engine itself, so even a missing EF Core filter cannot leak data. Implement a tenant provisioning API that creates the tenant record, provisions an admin user, and seeds default data in a single transaction. Add a data export endpoint that produces a complete JSON dump of a tenant's data for portability — required by GDPR's right to data portability. Finally, integrate the multi-tenant context with the audit log from this tutorial to a time-series store (InfluxDB or Azure Table Storage) so compliance queries can answer "who changed what, when?" without touching the live operational database.
Frequently Asked Questions
How do global query filters interact when multiple filters are applied to one entity?
EF Core ANDs all active global query filters together. If an entity has both a tenant filter and a soft-delete filter, the generated SQL will include WHERE TenantId = @tid AND IsDeleted = 0 automatically. IgnoreQueryFilters() disables ALL filters on that query at once — there is no API to selectively disable a single filter. If you need to bypass soft-delete but keep tenant isolation, you must manually add the tenant condition back to the query after calling IgnoreQueryFilters().
What is the safest way to run background jobs without leaking cross-tenant data?
Create a dedicated ExplicitTenantContext for background jobs — one that sets TenantId from the job payload rather than from an HTTP header. Never share a DbContext between jobs. For jobs that legitimately need to process data across all tenants, call IgnoreQueryFilters() deliberately, add explicit WHERE clauses, and document the intent with a code comment and PR reference. The risk of accidental cross-tenant access in background jobs is higher than in HTTP handlers because there is no request scope to naturally isolate contexts.
Should I use a shared database or a separate database per tenant?
Three options exist: shared database with shared schema (this tutorial), shared database with separate schemas, or separate database per tenant. Shared schema is cheapest to operate but offers the weakest isolation — a bug or missing filter leaks all tenants' data. Separate databases give the strongest isolation but cost the most. Choose based on your compliance requirements: contractual data residency or strict isolation requirements call for separate schemas or databases.
Why does EF Core query filter performance degrade without the right indexes?
Every query against a multi-tenant table has an implicit WHERE TenantId = @tid condition. Without an index starting with TenantId, the database performs a full table scan for every query. Add composite indexes starting with TenantId on every high-traffic table. For soft-delete, use a filtered index (WHERE IsDeleted = 0) to avoid index bloat from deleted rows that normal queries never touch.
How do I run EF Core migrations safely in a multi-tenant shared database?
Migrations operate at the schema level and don't know about tenants — run them once against the shared database, not once per tenant. The challenge is that global query filters can interfere with migration seed operations. Use a migration-specific DbContext that inherits from the main context but overrides OnModelCreating to skip filter registration. Alternatively, use a --migrate startup flag pattern (as shown in the Cloud-Native tutorial) that runs a stripped context before the full app starts.
Can I use soft deletes and still maintain unique constraints?
Not with a simple unique index, because deleted rows still occupy index space. Use a filtered unique index — UNIQUE WHERE IsDeleted = 0 — which excludes deleted rows from the uniqueness check. EF Core supports this via .HasFilter("[IsDeleted] = 0") on the index configuration. This allows a new row with the same unique key after the old one is soft-deleted, which is usually the desired behaviour for re-registration or re-creation scenarios.
How do I expose deleted records to admins without breaking normal queries?
Use IgnoreQueryFilters() scoped to dedicated admin repository methods — never as a boolean parameter on a general-purpose method. Create explicitly named methods like GetDeletedForTenantAsync() or GetAllTenantsIncludingDeletedAsync() that call IgnoreQueryFilters() and re-add whichever conditions still apply. The naming makes the bypass visible at every call site during code review.