ASP.NET Core with separate databases

Question

I want to create an ASP.NET Core web application with EF Core for multiple customers on a central server. Each customer should have its own database which gets filled by reading data from measuring devices that belong to the customer.

I found solutions which suggest to create multiple database contexts. The problem with this is that it is not scalable. I don't know the number of customers/databases in advance. Could be 10, could be 100.

Another solution could be to create separate applications in IIS (let's for simplicity only talk about Windows and IIS). Each application would have its own path and its own appsettings.json which specifies the database connection string. The problem with this is that it would take lots of disk space to duplicate the (self-contained) application and a lot of maintenance work.

Questions:

1) is there a way to create a list of database contexts dynamically, given multiple connection strings in my appsettings.json?

2) is there a way to keep only one physical installation in IIS but have multiple sites that each start the application with a different appsettings.json or maybe a start parameter that tells the app which connection string to use from appsettings.json?

3) is there a completely different and better way to achieve it?

Answer 1

I see this is an old question, but it is the scenario I'm working with now.

If your tenants have the same schema and different databases, the correct way would be to have one Master Context to retrieve the connection string and another Context to connect to the different tenants.

In a very common scenario where you access one tenant or another based on the host, this could be the solution:

1. get the connection string from the Master Context
2. Change the connection string for the TenantContext

3. Retrieve the data for that tenant

   public class ContextTenant : IdentityDbContext<ApplicationUser, 
       ApplicationRole, int>, IContextTenant
   {  
      private readonly string _tenantDatabase;
   
   public ContextTenant(DbContextOptions<ContextTenant> options, ITenantProvider tenantProvider) : base(options)
   {
       _tenantDatabase = tenantProvider.GetTenantDatabase();
       SeedData.Initialize(this);
   }
   
   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
       optionsBuilder.UseMySQL(_teanantDatabase);
       base.OnConfiguring(optionsBuilder);
   }
   
   ...
   create DbSets
   other operations
   ...
   }
   

Then retrieve the connection string:

public class WebTenantProvider : ITenantProvider
{
    private readonly string _tenantDatabase;

    public WebClientProvider(IHttpContextAccessor httpAccessor, ContextMaster dataContext)
    {
        var host = httpAccessor.HttpContext.Request.Host.Host;
        _tenantDatabase = dataContext.GetTenantDatabaseFromHost(host);
    }

    public string GetTenantDatabase()
    {
        return _tenantDatabase;
    }
}

public interface ITenantProvider
{
    string GetTenantDatabase();
}

Also your second quetion is interesting, because I have another problem here. My App localization resources are editable, I'm getting them instead of Regx files from the database, so each tenant has its own resources and they can edit them.

COuld it be possible to have just one application with all the files but start different instances of that application for different hosts? In case this would be possible we don't need the connection string thing since each instance would have all independent...