Best approach for entityframework core multi tenancy

Question

We develop a bigger SaaS application based on ASP.NET Core and EFCore. We separate tenants by database, means that we have one database for each tenant.

The database is migrated on any incoming request by a client by a custom middleware. This required, because the application should be immediately available after a tenant signed up on our SSO-Server.

So the pipeline looks like this:

1. Authentication Middleware (ASP.NET Core Standard)
2. UserResolverMiddleware (extracts TenantID from the JWT token and adds it to HttpContext.Items)
3. TenantDatabaseInitializerMiddleware (constructs a TenantDbContext object by passing the tenantid in the ConnectionString and starts migration)

Unfortunately this has some downsides, as I recognized later:

1. The TenantDatabaseInitializer sometimes uses an TenantDbContext of a different tenant. Therefore no database is migrated and the whole request fails.
2. If there are a lot of incoming requests for the same tenant, the migrations often overlap and fail. (The Middleware is catching the InvalidOperationExceptions that are thrown, but this does not seem as good solution).
3. It creates a lot of databases.
4. The requests take potentially long to complete (check for migration).

Long introduction, short question: Can anyone judge whether this migration workflow is a good practise? If not: Do you have a better idea how I can achieve multi tenancy?

I read about Global Query Filters in EFCore 2.0, but I'm not sure how safe this approach is regarding data isolation.

Thanks!

Answer 1

I think creating a database per each tenant is not practical. If you have 2000 tenants, then you have to maintain 2000 database instances!

I recommend using 1 database for all the application and tenants. And add a TenantId to most of tables so you can find out which tenant this specific records belong to.