Using EF Core, what is the most efficient way to dynamically switch schemas given a single DbContext?

Question

I own an application with ~500 users, and for security and separation of concerns, we use a separate schema to manage a user's data with tables that are identical in structure for everyone. I cannot create a new dbcontext class for each user, and they are dynamic so we create the schema via code (which works great). I recently figured out how to use EF migrations for each user's schema as well, so the only thing I'm struggling with is dynamically swapping the schema I wish to point to for each web request.

The scenario is very similar to the one that this answer (not the chosen answer) solves: https://stackoverflow.com/a/50529432 But this question was posted nearly 4 years ago, so I'm not sure that this answer is the most efficient or even the suggested way to go about it. The github docs point all questions to StackOverflow, so I'm hoping for an up-to-date workaround so that I can leverage the greatness that is EF without having to create my own entire implementation from scratch.

Answer 1

The normal way to accomplish this is to use separate databases per user/tenant. Then you can simply vary the connection string, instead of having separate schemas in a single database. Separate databases are more secure, enable scaling across multiple servers, and enable per-tenant backup and restore.

The referenced approach of using a custom ModelKeyCacheFactory to configure and cache a seperate DbContext model for each user should work. The downside is that you will configure and cache a large number of models, which is not free.

But to use schemas you do have another option. In EF Core 3.1 (at least) if you don't configure a schema for an Entity, and don't configure a default schema for the DbContext, object names will not be schema-qualified. So you can potentially use the schema resolution rules of your database to direct queries to the correct schema. In SQL Server for instance, you would provision a seperate database user for each tenant, with permissions on that tenant's schema, and with the tenant's schema configured as the user's default schema.

You could also potentially use Query Interception to rewrite the queries, after configuring the DbContext to insert some easy-to-replace token as the schema name.