Single Database, Schema-per-Tenant, and Database-per-Tenant Tradeoffs with DbContext Configuration

Learning outcomes

1. Tenancy topology is an operational architecture decision

Global filters solve one important problem: default row scoping inside a shared model. They do not answer whether every tenant should share the same physical database. ServiceHub now needs an explicit topology contract because backup/restore, maintenance windows, noisy-neighbor risk, connection pools, migrations, and incident blast radius differ more than the LINQ surface suggests.

2. Three common shapes and EF Core support

EF’s own multi-tenancy guidance explicitly marks schema-per-tenant as not supported. SQLite has no schemas at all, which makes the limitation even more concrete for the course baseline.

3. Shared database: simple model, hard security discipline

In the discriminator approach, all work-order rows share one table and every tenant-aware key/index/query must include tenant semantics where required. The database has one migration history and one backup unit. This is operationally efficient but tenant-specific restore is an application/data-recovery exercise rather than a simple database restore.

SELECT tenant_id, COUNT(*)FROM work_ordersWHERE is_deleted = 0GROUP BY tenant_id;-- One table, one file/database, multiple tenants.

4. Schema per tenant: why “just change ToTable schema” is fragile

Changing ToTable("work_orders", TenantSchema) changes the EF model shape. EF normally caches one model per context type, so dynamic schemas imply model-cache-key customization, separate migration targeting, and potentially unbounded cached models. More importantly, EF Core’s multi-tenancy documentation does not directly support this topology. Do not introduce it just to avoid a tenant column.

protected override void OnModelCreating(ModelBuilder modelBuilder){    modelBuilder.Entity<WorkOrder>()        .ToTable("work_orders", TenantSchema); // model shape now varies}

5. Database per tenant: choose the database before creating the context

public sealed class TenantDbContextFactory(    ITenantCatalog catalog,    ILoggerFactory logging){    public ServiceHubContext Create(string authorizedTenantId)    {        var target = catalog.ResolveRequired(authorizedTenantId);        var options = new DbContextOptionsBuilder<ServiceHubContext>()            .UseSqlite(target.ConnectionString)            .UseLoggerFactory(logging)            .Options;        return new ServiceHubContext(options, new FixedTenantContext(authorizedTenantId));    }}

Do not create a shared context and call Database.SetConnectionString while it is in use. Contexts are short-lived units of work; resolve the authorized target first and construct the context for that target.

6. Connection pools and database fan-out

On server providers, connection pools are typically partitioned by connection string. A database-per-tenant estate can therefore create many pools and server sessions, especially when each tenant gets a distinct database/catalog in the connection string. Capacity planning must include number of active tenants, pool limits, failover behavior, credentials, and idle connections. DbContext pooling is a separate mechanism and does not merge driver connection pools.

7. Deliberate failure: connection string from untrusted tenant input

var tenant = request.Query["tenant"];var cs = $"Data Source=servicehub-{tenant}.db";var options = new DbContextOptionsBuilder<ServiceHubContext>()    .UseSqlite(cs)    .Options;

Even with sanitized filenames, this lets the request choose a data partition. The repair is an authorization-derived tenant identity plus a server-owned tenant catalog mapping identity to a known connection target. Treat the catalog as security-critical configuration and audit changes to it.

8. Migrations are part of the topology contract

A shared database has one migration history. Database-per-tenant requires a fleet migration strategy: inventory target versions, migrate one coordinated owner, canary a subset, observe, continue, and record failures. Do not run migrations concurrently from every application instance. Schema-per-tenant multiplies this complexity further and is one reason it is not the default EF tenancy pattern.

tenant-a  servicehub-a.db  schemaVersion=2026082701  status=readytenant-b  servicehub-b.db  schemaVersion=2026082701  status=readytenant-c  servicehub-c.db  schemaVersion=2026082604  status=blocked-review

9. Mandatory free lab: compare shared-file and per-tenant-file SQLite

1. Create shared.db containing tenants A/B and apply the global filters from Lessons 1–2.
2. Create tenant-a.db and tenant-b.db with identical schema but only one tenant each.
3. Record file sizes, migration histories, and backup/restore steps for both shapes.
4. Demonstrate that a tenant-specific restore is trivial for the per-tenant file and a selective data operation for the shared file.
5. Record that schema-per-tenant is not reproducible on SQLite and is not directly supported by EF Core.
6. Delete all disposable files after the lab.

10. Production judgment and bridge

Choose a shared discriminator database when operational simplicity and shared infrastructure dominate and the application/database defenses are strong. Choose database-per-tenant when isolation, tenant-specific restore, regulatory boundaries, or independently scalable storage justify the fleet-management cost. Do not choose schema-per-tenant merely because the engine supports schemas; EF model/migration lifecycle matters. Lesson 4 turns from physical topology to the subtler runtime risk: cached models and pooled contexts carrying tenant-sensitive state.

Authoritative references

• EF Core multi-tenancy — official support table for discriminator, schema, and database-per-tenant patterns
• DbContext configuration — context construction/lifetime and connection configuration
• Advanced performance topics — DbContext pooling versus driver connection pooling
• Dynamic models — model caching when model shape varies
• EF Core migrations — migration lifecycle that multiplies across tenant databases
• SQLite limitations — no schemas/sequences and provider DDL boundaries