Architecture for DBContext in .Net Core application with multiple databases (1 per client)

user_801565 0
Viewed 276

I've inherited a .Net Framework application that wasn't really designed but thrown together as a portal application for clients, but then each client has their own database to store their data for security and data sovereignty reasons. The homepage loads basic data from a shared DB, but when the users naviagate to one of the pages, the site needs to connect to their specific database.

The app is written in C# with Angular and loosely follows MVC with the main context being passed around through DI, and I've just upgraded this to .Net 5. As there can be thousands of client databases that can't be registered as service connections in the startup, I'm trying to work out the best way to handle the DBContext for these client-specific databases. It seems to follow multi-tenancy principles in general, but the following are the concerns

  • Avoiding code repetition or adding lots of '= new DBContext' - use of DI would be preferable if possible with a TenantFactory or Database Interceptor
  • Ensuring that the connection is for the correct DB if the user has multiple tabs for different clients open
  • Ensuring that the DB connection is disposed of when the client page is closed or the user returns home to view another client

I realise that I can create a new DB Context per request, passing in the name of the DB each time, but is there a pattern or example that is better suited to this use case? Something like a DbCommandInterceptor at the DB level might work, but again if there are some examples of how to do this I can find them.

entity-framework-core dbcontext multi-tenant architecture c#
2 Answers

For your tenant context, make sure you use one of the AddDbContext overloads with a scoped optionsLifetime. Then you can provide a different connection string per instance, derived from any other scoped service.

services.AddDbContext<T>((sp, o) => {
    var context = sp.GetService<IHttpContextAccessor>().Context;
    o.UseSqlServer(... get the connection string here. From context.User? ...)
});
user_41398090

The simplest, most secure, and best way to do this is just what you said. Create a new DbContext per request, specifying the database name through some method of multi-tenancy isolation.

DbContext is designed to only live short term. Trying to make it live longer than a request will create memory and security issues. NEVER make a DbContext static (or singleton) in a multi-user application such as a web site.

The best practice is to inject a DbContext with a per request lifecycle. This will ensure the DbContext is destroyed after the request is completed, and all memory will be cleaned up.

Mystere_Man_611640
Related