Migrating a Single-Tenant SaaS to Multi-Tenant Workspaces with EF Core Global Query Filters

TL;DR

We had a single-tenant Angular + .NET 10 SaaS where every row was scoped by UserId. To support firms (multiple lawyers sharing data), we needed multi-tenant workspaces — but rewriting every query was off the table.

EF Core's HasQueryFilter made it possible to flip 9 tables to multi-tenant in a single weekend, with zero query call sites changed. Here's the pattern.

Context

I'm building NeoJurídico, a court-process monitoring platform for Colombian lawyers. Plan Bufete users need to share monitored processes, alerts, and notifications across their team.

Originally, every tenanted entity had int UserId as the implicit tenant. Service code looked like:

var processes = await _db.ProcessSubscriptions
    .Where(p => p.UserId == userId)
    .ToListAsync();

If we kept this pattern but added WorkspaceId next to it, every controller had to be touched. That's 60+ files. No.

The plan

1. Add int WorkspaceId to every tenanted entity (9 tables).
2. Mark them with ITenantedEntity interface.
3. Use HasQueryFilter so EF auto-injects WHERE WorkspaceId = @currentWs on every read.
4. Service code stays the same — p => p.UserId == userId — but now scoped automatically by workspace.
5. Backfill workspace ID on every existing row before flipping the column to NOT NULL (atomic migration).

The interface

namespace NEO.Domain.Workspaces;

public interface ITenantedEntity
{
    int WorkspaceId { get; set; }
}

Implemented by 9 entities: ProcessSubscription, NameMonitoring, Notification, Message, DisciplinaryRecord, UserProcess, JuridicProcess, MonitoringProcessMatch, Workspace itself excluded.

(Notably, ProcessReport — anonymous AI-generated reports paid by visitors with no account — is NOT tenanted. It lives against an email, not a user. We almost broke this by tagging it as ITenantedEntity in the migration. Caught it when the NOT NULL alter failed.)

The query filter

In NeoDbContext.OnModelCreating:

modelBuilder.Entity<ProcessSubscription>()
    .HasQueryFilter(p => p.WorkspaceId == _workspaceContext.CurrentWorkspaceIdOrNull());

Where IWorkspaceContext has two implementations:

// HTTP requests: get from JWT claim
public class HttpWorkspaceContext : IWorkspaceContext {
    private readonly IHttpContextAccessor _http;
    public int? CurrentWorkspaceIdOrNull() {
        var claim = _http.HttpContext?.User
            .FindFirst("active_workspace_id")?.Value;
        return int.TryParse(claim, out var id) ? id : null;
    }
}

// Background workers: explicit context, no http
public class BackgroundWorkspaceContext : IWorkspaceContext {
    private int? _workspaceId;
    public int? CurrentWorkspaceIdOrNull() => _workspaceId;
    public IDisposable EnterScope(int workspaceId) { ... }
}

The filter runs on every query EF Core generates. No service code changed.

The atomic migration

This is the part that scared me. We needed to:

1. Backfill WorkspaceId on existing rows (every user gets a "Personal" workspace; their data assigned to it)
2. Add NOT NULL constraint
3. Fail rollback on any error

Single migration, single transaction:

public partial class BackfillAndRequireWorkspaceId : Migration {
    protected override void Up(MigrationBuilder mb) {
        // Step 1: create personal workspace per user
        mb.Sql(@"
            INSERT INTO neo.workspaces (Name, OwnerUserId, Type, ...)
            SELECT COALESCE(NULLIF(TRIM(CONCAT(FirstName,' ',LastName)),''), 'Personal'),
                   Id, 'Personal', ...
            FROM neo.users u
            WHERE NOT EXISTS (
              SELECT 1 FROM neo.workspaces w
              WHERE w.OwnerUserId = u.Id AND w.Type = 'Personal'
            );
        ");

        // Step 2: insert Owner row in workspace_members
        mb.Sql(@"
            INSERT INTO neo.workspace_members (...)
            SELECT w.Id, w.OwnerUserId, 'Owner', 'Active', ...
            FROM neo.workspaces w
            WHERE w.Type = 'Personal'
              AND NOT EXISTS (...);
        ");

        // Step 3: backfill WorkspaceId in 9 tenanted tables
        mb.Sql(@"
            UPDATE neo.process_subscriptions t
            SET WorkspaceId = w.Id
            FROM neo.workspaces w
            WHERE w.OwnerUserId = t.UserId AND w.Type = 'Personal';
        ");
        // ... repeat for 8 more tables

        // Step 4: ALTER NOT NULL — runs in same transaction
        mb.AlterColumn<int>(name: "WorkspaceId", nullable: false, ...);
        // ... repeat for 8 more tables
    }
}

The key is keeping data backfill + NOT NULL alter in a single migration. If any step fails, the entire transaction rolls back. We won't end up with half-migrated tables.

Tested on QAS first. The migration ran in 2.3 seconds against ~5000 production rows.

The trap I almost fell into

HasQueryFilter with a captured DbContext field can throw a NullReferenceException in tests if the context isn't fully initialized:

// Don't do this:
.HasQueryFilter(p => p.WorkspaceId == _workspaceContext.CurrentWorkspaceId)
//                                                       ^^^ throws if null

// Do this instead:
.HasQueryFilter(p => p.WorkspaceId == _workspaceContext.CurrentWorkspaceIdOrNull())
//                                                       ^^^ returns int? — EF generates NULL-safe SQL

Returning nullable from the helper method makes EF generate IS NULL checks instead of crashing.

JWT claim for active workspace

The JWT now includes active_workspace_id. When a user switches workspace via the workspace switcher UI, the backend issues a fresh token:

[HttpPost("switch")]
public async Task<IActionResult> Switch(int workspaceId) {
    // Validate user is member with Active status
    var membership = await _db.WorkspaceMembers
        .FirstOrDefaultAsync(m => m.UserId == userId
                                && m.WorkspaceId == workspaceId
                                && m.Status == "Active");
    if (membership == null) return NotFound();

    // Issue new tokens with active_workspace_id claim
    return Ok(await _tokenService.IssueTokensAsync(userId, workspaceId));
}

Frontend stores the new token, all subsequent requests carry the new workspace context, EF query filter does the rest.

Plan-based feature gating

Plan Bufete unlocks multi-user. When user upgrades/downgrades, a MembershipReconciler runs:

• Plan upgrade to Bufete: existing dormant members → Active
• Plan downgrade from Bufete: members beyond MaxUsers → Dormant (data preserved)
• All transitions emit emails so the firm owner is informed

public async Task ReconcileAsync(int userId, int newPlanId) {
    var newPlan = await _db.SubscriptionPlans
        .FirstAsync(p => p.Id == newPlanId);
    if (!newPlan.MultiUser) {
        // Downgrade: deactivate excess members
        var members = await _db.WorkspaceMembers
            .Where(m => m.WorkspaceId == ownedWsId && m.Status == "Active")
            .OrderByDescending(m => m.JoinedAt)
            .Skip(1) // keep owner
            .ToListAsync();
        foreach (var m in members) m.Status = "Dormant";
    } else {
        // Upgrade: re-activate dormant
        var dormant = await _db.WorkspaceMembers
            .Where(m => m.WorkspaceId == ownedWsId && m.Status == "Dormant")
            .Take(newPlan.MaxUsers - 1).ToListAsync();
        foreach (var m in dormant) m.Status = "Active";
    }
}

This gracefully handles the "you exceeded your seat limit" case without ever deleting member data.

Takeaways

1. EF Core HasQueryFilter is the cleanest path to retrofit multi-tenancy onto an existing single-tenant schema. Zero service-code touches.
2. One migration, one transaction: backfill + NOT NULL alter together. Half-migrated state is the worst state.
3. Make ITenantedEntity opt-in by interface, not "every entity." We caught ProcessReport (anonymous payments) only because we had to explicitly add it.
4. Cache invalidation on workspace switch matters. Token issuance + frontend WorkspaceContextService → BehaviorSubject → re-fetch dashboard. Don't keep stale workspace data in app state.
5. JWT-claim-based context is testable, statelessly auditable, and survives horizontal scaling. No sticky sessions needed.

Migration shipped. 0 incidents. 0 query rewrites. Multi-tenant just worked.

---

I'm a solo founder building NeoJurídico for the Colombian legal market. If you're shipping multi-tenant features in EF Core or wrestling with audit + tenant isolation in .NET, happy to compare notes.