I'm building a backend service using ASP.NET5's Web API & EF7 to setup a multi tenancy database structure. The requirements are as follows:
I'm facing the following challenges at the moment:
To enable the service to dynamically add or remove tenants, my current implementation is based on a JSON config file, which contains all the tenant connection strings in key-value pairs, like this:
{
"Tenants": [
{ "Tenant1": "Server=.\\SQLEXPRESS;Database=Tenant1;integrated security=True;" },
{ "Tenant2": "Server=.\\SQLEXPRESS;Database=Tenant2;integrated security=True;" }
]
}
This configuration is then used to setup a ContextFactory. This factory uses a store of DbContextOptions in order to dynamically create DbContext instances when needed and therefore achieve the necessary short lifecycles. The factory is defined as follows:
public class TenantContextFactory : ITenantContextFactory
{
/// <summary>
/// The tenant configurations store.
/// </summary>
private IDictionary<string, DbContextOptions> tenants;
/// <summary>
/// Creates a new TenantContextFactory
/// </summary>
public TenantContextFactory()
{
tenants = new Dictionary<string, DbContextOptions>();
}
/// <summary>
/// Registers a tenant configuration with the store.
/// </summary>
/// <param name="id">The tenant id.</param>
/// <param name="options">The context options.</param>
public void RegisterTenant(string id, DbContextOptions options)
{
if (!tenants.ContainsKey(id))
{
tenants.Add(id, options);
}
}
/// <summary>
/// Creates a DbContext instance for the specified tenant.
/// </summary>
/// <typeparam name="T">The type of DbContext to create.</typeparam>
/// <param name="id">The tenant id.</param>
/// <returns>A new instance of the desired DbContext</returns>
public T GetTenantContext<T>(string id) where T : DbContext
{
DbContextOptions options;
if (tenants.TryGetValue(id, out options))
{
// get the type of the desired DbContext and return a new instance
// with the DbContextOptions as the constructor parameter
return (T)Activator.CreateInstance(typeof(T), options);
}
return null;
}
}
During the configuration phase, the ContextFactory is being filled with the tenant information using an extension method like this:
public static class ExtensionMethods
{
/// <summary>
/// Adds multi tenancy to the service.
/// </summary>
/// <param name="services">The service collection</param>
/// <param name="config">The configuration object</param>
public static void AddMultiTenancy(this IServiceCollection services, IConfiguration config)
{
var tenantContextFactory = new TenantContextFactory();
// get the information from the JSON file
var tenants = config.GetSection("Tenants");
var values = tenants.GetChildren();
foreach (var key in values)
{
foreach (var item in key.GetChildren())
{
// get the correct name of the config node
var tenantId = item.Key.Split(':').Last();
// and the connection string
var connectionString = item.Value;
// create the OptionsBuilder and configure it to use SQL server with the connection string
var builder = new DbContextOptionsBuilder();
builder.UseSqlServer(connectionString);
// and register it with the factory
tenantContextFactory.RegisterTenant(tenantId, builder.Options);
}
}
// register the factory with the DI container
services.AddInstance(typeof(ITenantContextFactory), tenantContextFactory);
}
}
The factory can then be injected as a service to any controller or service that needs it and instantiates the desired contexts correctly.
So far so good. The following questions remain:
How to integrate EF7 migrations? (solved)
Trying to add migrations I get the following error:
System.InvalidOperationException: No database providers are configured. Configure a database provider by overriding OnConfiguring in your DbContext class or in the AddDbContext method when setting up services.
Since the number of tenants is dynamic, I cannot specify a connection string directly in the DbContext class, or use the AddDbContext method to statically register DbContexts with a single database.
When I do supply a static connection string, the migrations get created successfully, but when I then try to use my dynamic approach, those migrations are not applied to the databases in use and I have no way to specify a connection string in the EF shell commands in order to execute migrations manually or via a shell script. I would basically have to rewrite the configuration code once per tenant, recompile and then use a shell command to execute the migrations, which is not a worthwhile option.
SOLUTION:
The Contexts to be migrated by using the following snippet for every context you want to use:
using (var context = tenantContextFactory.GetTenantContext<MyContext>(tenantId))
{
context.Database.Migrate();
}
This checks the database automatically if the schema is compliant with the latest migrations, and applies it when not.
How to integrate ASP.NET Identity?
The authentication process needs to be tweaked to log in users correctly.
I'm on it right now and will post updates on my progress here.
How to change tenants at runtime?
This ties into the previous questions. How can I ensure that I can safely add or remove tenants by editing the config file without having to restart the service? Is that even possible?
EDIT:
I've found the solution to the migrations problem in EF7. The next challenge is ASP.NET Identity.
As many have suggested it would be a lot cleaner to have one database. See my comment in the discussions..
In the database have a Provider/Source Table.
Then create Repo's which have to accept a SourceId.
public interface IRepository<T>
{
T GetById(int sourceid, int id);
}
public class BaseRepository<TEntity> : IRepository<TEntity> where TEntity : class
{
public TEntity GetById(int sourceId, int id)
{
return _dbContext.Set<TEntity>().Find(sourceId, id);
}
}
I know this would require you to have SourceId on basically every table.
I know this isn't the answer your looking for but maybe something to think about.
For me the code quite ambitious and will be very complex to maintain.
But I do hope you get it right!
Update
using(var context = new MyContext(DbHelper.GetConnectionString()))
{
}
public static class DbHelper
{
public static string GetConnectionString()
{
//some logic to get the corrosponding connection string
// which you are wanting this could be based of url
}
}