.net Core 2, EF and Multi Tenancy - Dbcontext switch based on user

Question

I have the (almost) worst of multi tenancy. I'm building a asp.net core website that I'm porting a bunch of pokey little intranet sites to. Each subsite will be an asp.net Area. I have an IdentityContext for the Identity stuff. I have multiple copies of vendor databases, each of those with multiple tenants. The ApplicationUserclass has an OrgCode property that I want to use to switch the db context.

I can see myself needing something that maps User.OrgCode and Area to a Connection string

There are many partial examples of this on Stack Overflow. I am very confused after an afternoons reading. The core of it seams to be:

• remove DI dbcontext ref from the constructor args.
• Instantiate the dbcontext in the controller constructor.
• Use dbcontext as before.

Am I on the right track?

Any coherent examples?

---

Edit 2020/07/09

This has unfortunately become more pressing.

The Identity database is tenant agnostic. Every user in Identity has an OrgCode identifier. (Custom user property).

Each server has multi tenancy built in through the use of 'cost centers'. The server has a collection of databases named the same on every server.

1. core vendor database
2. custom database where we store our extensions
3. logs database for our job output

There are also small application specific databases that already use an Org Code to identify a user

Server A - 1 Org Code

Server B - 4 Org Codes

Server C - 3 Org Codes engaged in project, 50+ not yet (mostly small)

Server D - No Org Codes engaged as of now. 80+ on server. (soon)

It is not possible to consolidate all the organisations onto one server. There are legal and technical ramifications. Each server has hundreds of remote transponders reporting to them that would need updating. The data these supply is what our custom jobs work with.

The dream is to continue to use DI in each page, passing in the contexts as required. The context would then be smart enough to pick the correct underlying connection details based on the OrgCode of the username.

I hesitate to use the word proxy because it seems heavily loaded in this space.

Hell, even using a switch statement would be fine if I knew where to put it

Desired effect User from Org XYZ loads page that requires Vendor database, they get the one from the server that XYZ maps to.

Edit 2020/07/13

To tidy up referenceing, I've switched the OrgCode and Server to Enums. The context inheritance is as follows

• DbContext

  • CustLogsContext

       public virtual ServerEnum Server 
       { 
           get 
           { 
               return ServerEnum.None; 
           }
       }
    
       DbSet (etc)
    

    • CustLogsServerAContext

           public override ServerEnum Server 
           { 
               get 
               { 
                   return ServerEnum.ServerA; 
               }
           }
      

    • CustLogsServerBContext (etc)

    • CustLogsServerCContext (etc)

    • CustLogsServerDContext (etc)

  • VendorContext

    • VendorServerAContext
    • VendorServerBContext (etc)
    • VendorServerCContext (etc)
    • VendorServerDContext (etc)

I've also created a static class OrgToServerMapping that contains a dictionary mapping OrgCodes to Servers. Currently hardcoded, will change eventually to load from config, and add a reload method.

Currently thinking I need a class that collects the contexts Would have a Dictionary<serverEnum, dbcontext> and be registered as a service. Pretty sure I'd need a version of the object for each inherited dbcontext, unless someone knows ome polymorphic trick I can use

Answer 1

I work on a similar system with thousands of databases, but with LinqToSql instead of EF (I know...). Hopefully the general ideas translate. There are connection pool fragmentation issues that you have to contend with if you end up with many databases, but for just your four databases you won't have to worry about that.

I like these two approaches - they both assume that you can set up the current ApplicationUser to be injected via DI.

Approach #1: In Startup, configure the DI that returns the data context to get the current user, then use that user to build the correct data context. Something like this:

// In Startup.ConfigureServices
services.AddScoped<ApplicationUser>((serviceProvider) =>
{
    // something to return the active user however you're normally doing it.
});

services.AddTransient<CustLogsContext>((serviceProvider) =>
{
    ApplicationUser currentUser = serviceProvider.GetRequiredService<ApplicationUser>();

    // Use your OrgToServerMapping to create a data context 
    // with the correct connection
    return CreateDataContextFromOrganization(currentUser.OrgCode);
});

Approach #2: Rather than injecting the CustLogsContext directly, inject a service that depends on the active user that is responsible for building the data context:

// In Startup.ConfigureServices
services.AddScoped<ApplicationUser>((serviceProvider) =>
{
    // something to return the active user however you're normally doing it.
});
services.AddTransient<CustLogsContextWrapper>();

// In its own file somewhere
public class CustLogsContextWrapper
{
    private ApplicationUser currentUser;
    public CustLogsContextWrapper(ApplicationUser currentUser)
    {
        this.currentUser = currentUser;
    }

    public CustLogsContext GetContext()
    {
        // use your OrgToServerMapping to create a data context with the correct connection;
        return CreateDataContextFromOrganization(user.OrgCode);
    }
}

Personally I prefer the latter approach, because it avoids a call to a service locator in Startup, and I like encapsulating away the details of how the data context is created. But if I already had a bunch of code that gets the data context directly with DI, the first one would be fine.

Answer 2

I have created a multitenancy implementation as follows (which could scale endlessly in theorie). Create a multitenancy database (say tenantdb). Easy. But the trick is to store connectionstring details for each tenant (your target databases). Along side your user orgCode etc.

I can see myself needing something that maps User.OrgCode and Area to a Connection string

So the way to map it in code is to feed your dbcontext whith your target tenant connectionstring, which you get from your tenantdb. So you would need anohter dbcontext for you tenantdb. So first call your tenantdb get the correct tenant connectionstring by filtering with your user orgcode. And then use it to create a new target dbcontext.

The dream is to continue to use DI in each page, passing in the contexts as required. The context would then be smart enough to pick the correct underlying connection details based on the OrgCode of the username.

I have this working with DI.

I created UI elements for crud operations for this tenantdb, so I can update delete add connection string details and other needed data. The Password is encrypted on save and decrypted on the get just before passing to your target dbcontext.

So I have two connection strings in my config file. One for the tenantdb and one for a default target db. Which can be an empty/dummy one, as you probably encounter application startup errors thrown by your DI code if you don't have one, as it will most likely auto search for a connectionstring.

I also have switch code. This is where a user can switch to anohter tenant. So here the user can choose from all the tenants it has rights to (yes rights are stored in tenantdb). And this would again trigger the code steps described above.

Cheers.

Took this Razor Pages tutorial as my starting point.

This way you can have very lousily coupled target databases. The only overlap could be the User ID. (or even some token from Azure,Google,AWS etc)

Startup.

 public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddRazorPages();

        services.AddDbContext<TenantContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("TenantContext")));

        //your dummy (empty) target context.
        services.AddDbContext<TargetContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("TargetContext")));
    }

IndexModel (Tenant pages).

public class IndexModel : PageModel
{
    private readonly ContosoUniversity.Data.TenantContext _context;
    private ContosoUniversity.Data.TargetContext _targetContext;

    public IndexModel(ContosoUniversity.Data.TenantContext context, ContosoUniversity.Data.TargetContext targetContext)
    {
        _context = context;
        //set as default targetcontext -> dummy/empty one.
        _targetContext = targetContext;
    }

    public TenantContext Context => _context;

    public TargetContext TargetContext { get => _targetContext; set => _targetContext = value; }

    public async Task OnGetAsync()
    {
        //get data from default target.
        var student1 = _targetContext.Students.First();

        //or
        //switch tenant
        //lets say you login and have the users ID as guid.
        //then return list of tenants for this user from tenantusers. 
        var ut = await _context.TenantUser.FindAsync("9245fe4a-d402-451c-b9ed-9c1a04247482");
        
        //now get the tenant(s) for this user.
        var SelectedTentant = await _context.Tenants.FindAsync(ut.TenantID);
        
        DbContextOptionsBuilder<TargetContext> Builder  = new DbContextOptionsBuilder<TargetContext>();
        Builder.UseSqlServer(SelectedTentant.ConnectionString);
        _targetContext = new TargetContext(Builder.Options);

        //now get data from the switched to database.
        var student2 = _targetContext.Students.First();
    }
}

Tenant.

 public class Tenant
{
    public int TenantID { get; set; }
    public string Name { get; set; }
    //probably could slice up the connenctiing string into props.
    public string ConnectionString { get; set; }

    public ICollection<TenantUser> TenantUsers { get; set; }
}

TenantUser.

public class TenantUser
{
    [Key]
    public Guid UserID { get; set; }
    public string TenantID { get; set; }
}

Default connstrings.

{ "AllowedHosts": "*", "ConnectionStrings": { "TenantContext": "Server=(localdb)\mssqllocaldb;Database=TenantContext;Trusted_Connection=True;MultipleActiveResultSets=true", "TargetContext": "Server=(localdb)\mssqllocaldb;Database=TargetContext;Trusted_Connection=True;MultipleActiveResultSets=true" }