How to setup multi tenancy in ASP.NET 5 Web API and Entity Framework 7?

asp.net-core asp.net-web-api c# entity-framework-core

Question

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:

  • API endpoints are the same for all tenants,
  • each tenant has its own database containing tenant specific data,
  • all tenants use the same database structure, therefore the same DbContext classes can be used across all tenants, but with different connection strings,
  • each tenant database contains user information for authentication based on ASP.NET Identity.

I'm facing the following challenges at the moment:

  • DbContext instances are not thread-safe, therefore their lifecycle should be short. This means I cannot simply store instances of DbContexts somewhere, but have to dynamically create and dispose instances when needed,
  • It must be possible to add or remove tenants dynamically, preferably without restarting the service,
  • EF7 migrations need to work.

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.

1
6
9/30/2015 10:12:02 PM

Popular Answer

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
    }
}
0
9/24/2015 11:51:45 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow