Create DbContext dynamically for multiple database architecture with repect to logged-in user

Question

I am working on multi layered architecture with supporting multiple database per tenant in MVC. I need to create DBContext depending on the user logged in into data layer. How should I create generic DBContext and set connection string according to user?

Following is code to get connection string from Host Databsae and set it to Client Database. (In data/repository layer)

private static string GetConnectionString()
{
    int tenantId;
    HostDBContext hostContext = new HostDBContext();

    //Get Tenant and set ID to get required connection string
    tenantId = 1; //Get Tenant Id from session

    var tenantDetails = hostContext.TenantDetails.Find(tenantId);

    if (tenantDetails != null)
    {
        return tenantDetails.connectionstring;
    }

    return "default connection string";
}

Have multiple databases (per client) is very suspect approach to any problem, especially when you have schema changes, and all sorts of other scaling issues. 2 cents> Multitenancy is usually always a better approach unless severe privacy and security needs implementing — TheGeneral, Jan 03 '19 at 07:51
@SwapnilBapat so just need to pass the connection staring to the DBContext ? — Mohamed Elrashid, Jan 03 '19 at 08:33
@MohamedElrashid, thats over simplification. but yes. One thing is I am not using connection string form configuration file instead I will be fetching it from Database. I need to find a way so that I could create my DBContext to get logged in users tenant id and respectively fetch that connection string — Swapper, Jan 03 '19 at 09:26
you can use two DBContext class one for asp.net identity and other for the user db ? what is missing ? — Mohamed Elrashid, Jan 03 '19 at 09:56
Maybe you should look on some framework which implement such multitenancy and copy solution? For example Asp.Net Boilerplate have such feature. — CrazyBaran, Jan 03 '19 at 10:01
@CrazyBaran, Sure, but the thing is we already have some table schema developed and we also have too many custamizations (in terms of user roles, table scemas etc). Customizing in those frameworks will be very time consuming and headache. — Swapper, Jan 03 '19 at 12:14
@MohamedElrashid,Yes thats done, I am not sure how I should create DBContext object whenever is requested with logged in user's connection string as it is in Data Layer (tenant id). I don't want to send tenant id as parameter to my services in order to create dbcontext. — Swapper, Jan 03 '19 at 12:18
@Swapper I didint told you that you should implement this framework, for existing project it will be end with rewrite all code. I only propose that you should look how it is made in this framework and reimplement this. For shure it is better sollution than rethinking this alone. — CrazyBaran, Jan 03 '19 at 14:18
how may users in one Tenant ? how are you hosting it in Azure, AWS ? and What server IIS, Nginx ? Do know how Slack.com works? Slack.com use a different URL for each Tenant ? and use the url as Tenant id — Mohamed Elrashid, Jan 03 '19 at 15:01

score 1 · Answer 1 · answered Jan 03 '19 at 09:50

You're on the right track, but it won't be a generic DbContext, just one that with a connection string that's set at runtime. So instead of GetConnectionString, you want to create a DbContext factory that requires the tenant Id to return your new DbContext("connectionString").

Let's call it TenantDbContext:

public class TenantDbContext : DbContext
{
    public TenantDbContext(string connectionString) : base(connectionString)
    {
    }
// Or
    public TenantDbContext(int tenantId) : base(GetConnectionString(tenantId))
    {
    }
}

if using a factory:

public class TenantDbContextFactory : IDbContextFactory<TenantDbContext, int>
{
    public TenantDbContext Create(int tenantId)
    {
        var connectionString = GetConnectionString(tenantId);
        return new TenantDbContext(connectionString);
    }
    // ... rest of factory
}

public interface IDbContextFactory<TContext, TKey> where TContext :DbContext
{
     TContext Create(TKey key);
}

If you're using a dependency injection container, you can also wire it up to return the TenantDbContext based on the tenant Id.

Create DbContext dynamically for multiple database architecture with repect to logged-in user

1 Answers1