I recently needed to tackle this problem to develop a reliable multi-tenant connection capable of supporting not only tenant DBs being located on different servers (which can be resolved using the connection string) but also supporting tenant-per-schema. The tricky bit here is that your entity mapping needs to reflect the schema name for resolving the table. This assumes that all tenants use the same DB schema, just different schema names.
In my case I was using the Mehdi.me DbContextScope which went part-way to supporting what I needed since you can override and provide an IDbContextFactory implementation to create your DbContexts using the appropriate connection string. The second bit I built a bit of an extension to the factories to ensure that tenant schema details could get through to the initialization of the entity configuration.
Feel free to have a look to see if it might be suitable for your project, or give you some ideas how to get what you need. (https://github.com/StevePy/DbContextScope)
The implementation takes a bit of setup, and obviously getting used to the DbContextFactory/DbContextLocator pattern for unit of work but you roughly need the following setup:
Creating a class to represent your tenant connection details implementing IDbTenant. This class would be linked to the current tenant instance is return the connection string and schema name of where the tenant's tables will be located.
An implementation of IDbContextFactory in your project which will construct the DbContext instance. This factory normally accepts a default constructor, a connection string, and now an instance of the IDbTenant created is step 1.
Initialize your IoC if present to initialize the DbContextScopeFactory with the IDbContextFactory created in step 2. This would look something like:
ioc.Register<IDbContextScopeFactory>( ()=> {new DbContextScopeFactory(new SqlServerTenantDbContextFactory());});
Where SqlServerTenantDbContextFactory is the implementation created in step 2. The above is roughly the registration process for Autofac IoC. Essentially you just want to ensure when a DbContextScopeFactory is instantiated, you provide it your DbContextFactory.
An implementation of the ContextFactory.
public class SqlServerTenantDbContextFactory : IDbContextFactory
{
TDbContext IDbContextFactory.CreateDbContext<TDbContext>()
{
return (TDbContext)Activator.CreateInstance<TDbContext>();
}
TDbContext IDbContextFactory.CreateDbContext<TDbContext>(IDbTenant tenant)
{
var connection = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection();
// based on the provider set up in <providers> configration under <entityFramework>...
connnection.ConnectionString = tenant?.ConnectionString;
return (TDbContext)Activator.CreateInstance(typeof(TDbContext), tenant, connection, true);
}
TDbContext IDbContextFactory.CreateDbContext<TDbContext>(string connectionString)
{
var connection = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection();
connnection.ConnectionString = connectionString;
return (TDbContext)Activator.CreateInstance(typeof(TDbContext), connection, true);
}
}
The last configuration change is for the EntityTypeConfiguration to add a constructor that accepts an IDbTenant and add the [ImportingConstructor] attribute to that constructor. The ContextFactory will take care of the rest. So for example, given an entity called "Order", you would define an entity type configuration like:
public class OrderConfiguration : EntityTypeConfiguration<Order>
{
[ImportingConstructor]
public OrderConfiguration(IDbTenant tenant)
: base()
{
ToTable("Orders", tenant.SchemaName);
// HasKey(...);
// HasMany(...);
// etc. etc. etc.
}
}
Not sure if this can be adapted to serve code-first implementations, but I'd doubt it since that pattern wants to take responsibility for the db schema definition and migration which would be a mess when it comes to switching between schemas or servers.
It's probably a lot to take in, especially if you haven't had previous experience with the Mehdi.me DbContextScope, but hopefully it might give you some ideas.
Once these are set, the only change from the normal Mehdi.me pattern is that when you go to retrieve the context using the DbContextLocator, pass it an instance of the IDbTenant instance:
private MyAppContext Context
{
get { return ContextLocator.Get<MyAppContext>(_tenant); }
}
where _tenant is initialized based on your logged in tenant. (I.e. tenant identification strategy pulling the details from OWIN auth, or session state...) From here the context factory takes over and will initialize your context using the tenant details.
