Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 41 ยท Projects and Practice

Project: A Multi-Tenant SaaS Data Layer

Design a shared-database multi-tenant data layer with EF Core: tenant filters, automatic tenant stamping, audit trail, soft delete and isolation tests, with runnable C# stand-ins.

A multi-tenant application serves many customer organizations (tenants) from one deployment. Acme Corp and Globex must never see each other's invoices, even though their rows may sit in the same table. In this project you build the data layer for a small invoicing SaaS: tenant isolation, automatic stamping of the tenant and audit fields, soft delete, and tests that prove the isolation works.

EF Core code is read-only on this site. The runnable blocks are stand-ins for the logic.

Choosing a tenancy strategy

Strategy Isolation Cost Notes
Shared database, shared schema (a TenantId column) logical cheapest what we build; needs discipline
Schema per tenant stronger more migrations to run uncommon with EF Core
Database per tenant strongest most ops work choose the connection string per request

We use the first option. Its weakness is that one forgotten filter leaks data, so we add several layers of safety.

Step 1: knowing the current tenant

C#
public interface ITenantProvider
{
    Guid TenantId { get; }
}

public class HttpTenantProvider(IHttpContextAccessor accessor) : ITenantProvider
{
    public Guid TenantId =>
        Guid.TryParse(accessor.HttpContext?.User.FindFirst("tenant_id")?.Value, out var id)
            ? id
            : throw new InvalidOperationException("No tenant for this request.");
}

The tenant comes from a trusted claim in the authenticated user's token, never from a query string or header the client controls. Register the provider as scoped (see DbContext lifetimes).

Step 2: tenant-owned entities and the global filter

C#
public interface ITenantOwned
{
    Guid TenantId { get; set; }
}

public interface IAuditable
{
    DateTime CreatedAt { get; set; }
    string? CreatedBy { get; set; }
    DateTime? UpdatedAt { get; set; }
}

public class Invoice : ITenantOwned, IAuditable
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string Number { get; set; } = "";
    public decimal Total { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime CreatedAt { get; set; }
    public string? CreatedBy { get; set; }
    public DateTime? UpdatedAt { get; set; }
}

public class AppDbContext(DbContextOptions<AppDbContext> options, ITenantProvider tenant) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Invoice>(e =>
        {
            e.HasQueryFilter(i => i.TenantId == tenant.TenantId && !i.IsDeleted);
            // invoice numbers are unique per tenant, not globally
            e.HasIndex(i => new { i.TenantId, i.Number }).IsUnique();
            e.Property(i => i.Total).HasPrecision(18, 2);
        });
    }
}

Two details matter. The filter reads tenant.TenantId from a captured service on the context, so EF Core evaluates it per query (see global query filters). And the unique index starts with TenantId, so each tenant has its own numbering, and every tenant query can use the index.

C# Stand-in: tenant filter isolates two customers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Step 3: stamp tenant and audit fields on save

Do not trust application code to set TenantId on every new invoice. Stamp it in a SaveChangesInterceptor, and refuse changes that touch another tenant's rows:

C#
public class TenantAuditInterceptor(ITenantProvider tenant, ICurrentUser user, TimeProvider clock)
    : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData e, InterceptionResult<int> result, CancellationToken ct = default)
    {
        var now = clock.GetUtcNow().UtcDateTime;

        foreach (var entry in e.Context!.ChangeTracker.Entries())
        {
            if (entry.Entity is ITenantOwned owned)
            {
                if (entry.State == EntityState.Added)
                    owned.TenantId = tenant.TenantId;
                else if (entry.State is EntityState.Modified or EntityState.Deleted
                         && owned.TenantId != tenant.TenantId)
                    throw new InvalidOperationException("Cross-tenant write blocked.");
            }

            if (entry.Entity is IAuditable a)
            {
                if (entry.State == EntityState.Added) { a.CreatedAt = now; a.CreatedBy = user.Id; }
                if (entry.State == EntityState.Modified) a.UpdatedAt = now;
            }

            if (entry.State == EntityState.Deleted && entry.Entity is Invoice inv)
            {
                entry.State = EntityState.Modified;      // soft delete
                inv.IsDeleted = true;
            }
        }
        return base.SavingChangesAsync(e, result, ct);
    }
}
C# Stand-in: stamping, soft delete and blocking cross-tenant writes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Step 4: defense in depth

A filter plus an interceptor still leaves gaps. Close them:

  • Raw SQL and IgnoreQueryFilters() bypass the filter. Search the code base for both in code review, and wrap legitimate admin use in a clearly named service.
  • Bulk operations (ExecuteUpdate, ExecuteDelete, see bulk operations) do respect the model's query filters when built from a filtered DbSet, but verify that in a test for your EF Core version.
  • Database row-level security (PostgreSQL policies, SQL Server security predicates) enforces isolation even if application code is wrong. Set the tenant in a connection interceptor (SET app.tenant = ...) at the start of each connection use.
  • Foreign keys across tenants: an invoice line must reference a product of the same tenant. Use a composite foreign key (TenantId, ProductId) so the database refuses cross-tenant references.
  • Caches keyed without the tenant leak data; always include TenantId in cache keys.

Step 5: prove isolation with tests

The most valuable test of a multi-tenant system creates data for two tenants and asserts that every query path only sees one (see testing EF Core code):

C#
[Fact]
public async Task Tenant_cannot_read_or_change_other_tenants_invoices()
{
    var globexInvoiceId = await SeedAsync(tenant: Globex, number: "INV-001");

    await using var db = fixture.CreateContext(tenant: Acme);

    Assert.Empty(await db.Invoices.ToListAsync());
    Assert.Null(await db.Invoices.FindAsync(globexInvoiceId));        // FindAsync honors filters? Verify!
    Assert.Equal(0, await db.Invoices
        .Where(i => i.Id == globexInvoiceId)
        .ExecuteUpdateAsync(s => s.SetProperty(i => i.Total, 0m)));
}

Note the comment: behaviors like Find and the tracker's identity map differ from queries, and filters apply to queries. Writing the test is how you learn what your version really does.

C# Stand-in: an isolation test over every access path

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Extension ideas

  1. Add a Plan entity per tenant and enforce limits (for example 100 invoices) in the interceptor.
  2. Support database-per-tenant for premium customers: choose the connection string from a tenant catalog in OnConfiguring, and think about migrations for hundreds of databases.
  3. Add an AuditLog table written by the interceptor with the old and new values from entry.OriginalValues and entry.CurrentValues (see change tracking).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where should the current tenant ID come from?

    Anything the client controls can be forged. Use a server-verified claim.
  2. 2. Why does the unique index start with TenantId?

    Each tenant has its own numbering, and the leading column matches the filter predicate.
  3. 3. What is the interceptor's job in this design?

    Centralizing these in one save hook means no code path can forget them.
  4. 4. Which of these defeats a global tenant filter?

    Both bypass the model-level predicate, so review them carefully and add database-level protection.

Summary

  • Shared-schema multi-tenancy uses a TenantId column, a global query filter and a save interceptor.
  • Take the tenant from a trusted claim and include it in unique indexes, foreign keys and cache keys.
  • Add defense in depth: review bypasses, consider row-level security, and write isolation tests for every access path.

Next: EF Core practice, troubleshooting and interview questions