After a few hours of research and finding no way to do this; it's time to ask the question.
I have an ASP.NET Core 1.1 project using EF Core and MVC that is used by multiple customers. Each customer has their own database with the exact same schema. This project is currently a Windows application being migrated to the web. At the login screen the user has three fields, Company Code, Username and Password. I need to be able to change the connection string when the user attempts to login based on what they type in the Company Code input then remember their input throughout the session duration.
I found some ways to do this with one database and multiple schema, but none with multiple databases using the same schema.
The way I solved this problem isn't an actual solution to the problem, but a work around that worked for me. My databases and app are hosted on Azure. My fix to this was to upgrade my app service to a plan that supports slots (only an extra $20 a month for 5 slots). Each slot has the same program but the environment variable that holds the connection string is company specific. This way I can also subdomain each companies access if I want. While this approach may not be what others would do, it was the most cost effective to me. It is easier to publish to each slot than to spend the hours doing the other programming that doesn't work right. Until Microsoft makes it easy to change the connection string this is my solution.
In response to Herzl's answer
This seems like it could work. I have tried to get it implemented. One thing I am doing though is using a repository class that accesses my context. My controllers get the repository injected into them to call methods in the repository that access the context. How do I do this in a repository class. There is no OnActionExecuting overload in my repository. Also, if this persists for the session, what happens when a user opens their browser to the app again and is still logged in with a cookie that lasts 7 days? Isn't this a new session? Sounds like the app would throw an exception because the session variable would be null and therefor not have a complete connection string. I guess I could also store it as a Claim and use the Claim if the session variable is null.
Here is my repository class. IDbContextService was ProgramContext but I started adding your suggestions to try and get it to work.
public class ProjectRepository : IProjectRepository
{
private IDbContextService _context;
private ILogger<ProjectRepository> _logger;
private UserManager<ApplicationUser> _userManager;
public ProjectRepository(IDbContextService context,
ILogger<ProjectRepository> logger,
UserManager<ApplicationUser> userManger)
{
_context = context;
_logger = logger;
_userManager = userManger;
}
public async Task<bool> SaveChangesAsync()
{
return (await _context.SaveChangesAsync()) > 0;
}
}
In response to The FORCE JB's answer
I tried to implement your approach. I get an exception in Program.cs on line
host.Run();
Here is my 'Program.cs' class. Untouched.
using System.IO;
using Microsoft.AspNetCore.Hosting;
namespace Project
{
public class Program
{
public static void Main(string[] args)
{
var host = new WebHostBuilder()
.UseKestrel()
.UseContentRoot(Directory.GetCurrentDirectory())
.UseIISIntegration()
.UseStartup<Startup>()
.Build();
host.Run();
}
}
}
And my 'Startup.cs' class.
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Serialization;
using System;
using System.Collections.Generic;
using Project.Entities;
using Project.Services;
namespace Project
{
public class Startup
{
private IConfigurationRoot _config;
public Startup(IHostingEnvironment env)
{
var builder = new ConfigurationBuilder()
.SetBasePath(env.ContentRootPath)
.AddJsonFile("appsettings.json")
.AddEnvironmentVariables();
_config = builder.Build();
}
public void ConfigureServices(IServiceCollection services)
{
services.AddSingleton(_config);
services.AddIdentity<ApplicationUser, IdentityRole>(config =>
{
config.User.RequireUniqueEmail = true;
config.Password.RequireDigit = true;
config.Password.RequireLowercase = true;
config.Password.RequireUppercase = true;
config.Password.RequireNonAlphanumeric = false;
config.Password.RequiredLength = 8;
config.Cookies.ApplicationCookie.LoginPath = "/Auth/Login";
config.Cookies.ApplicationCookie.ExpireTimeSpan = new TimeSpan(7, 0, 0, 0); // Cookies last 7 days
})
.AddEntityFrameworkStores<ProjectContext>();
services.AddScoped<IUserClaimsPrincipalFactory<ApplicationUser>, AppClaimsPrincipalFactory>();
services.AddScoped<IProjectRepository, ProjectRepository>();
services.AddTransient<MiscService>();
services.AddLogging();
services.AddMvc()
.AddJsonOptions(config =>
{
config.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
});
}
public void Configure(IApplicationBuilder app, ILoggerFactory loggerFactory)
{
Dictionary<string, string> connStrs = new Dictionary<string, string>();
connStrs.Add("company1", "1stconnectionstring"));
connStrs.Add("company2", "2ndconnectionstring";
DbContextFactory.SetDConnectionString(connStrs);
//app.UseDefaultFiles();
app.UseStaticFiles();
app.UseIdentity();
app.UseMvc(config =>
{
config.MapRoute(
name: "Default",
template: "{controller}/{action}/{id?}",
defaults: new { controller = "Auth", action = "Login" }
);
});
}
}
}
And the exception:
InvalidOperationException: Unable to resolve service for type 'Project.Entities.ProjectContext' while attempting to activate 'Microsoft.AspNetCore.Identity.EntityFrameworkCore.UserStore`4[Project.Entities.ApplicationUser,Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityRole,Project.Entities.ProjectContext,System.String]'.
Not sure what to do here.
Partial success edit
Okay I got your example working. I can set the connection string in my repository constructor using a different id. My problem now is logging in and choosing the right database. I thought about having the repository pull from a session or claim, whatever wasn't null. But I can't set the value before using the SignInManager in the Login controller because SignInManager is injected into the controller which creates a context before I update the session variable. The only way I can think of is to have a two page login. The first page will ask for the company code and update the session variable. The second page will use the SignInManager and have the repository injected into the controllers constructor. This would happen after the first page updates the session variable. This may actually be more visually appealing with animations between both login views. Unless anyone has any ideas on a way to do this without two login views I am going to try and implement the two page login and post the code if it works.
It is actually broken
When it was working, it is because I still had a valid cookie. I would run the project and it would skip the login. Now I get the exception InvalidOperationException: No database provider has been configured for this DbContext after clearing my cache. I have stepped through it all and the context is being created correctly. My guess is that Identity is having some sort of issues. Could the below code adding the entity framework stores in ConfigureServices be causing the issue?
services.AddIdentity<ApplicationUser, IdentityRole>(config =>
{
config.User.RequireUniqueEmail = true;
config.Password.RequireDigit = true;
config.Password.RequireLowercase = true;
config.Password.RequireUppercase = true;
config.Password.RequireNonAlphanumeric = false;
config.Password.RequiredLength = 8;
config.Cookies.ApplicationCookie.LoginPath = "/Company/Login";
config.Cookies.ApplicationCookie.ExpireTimeSpan = new TimeSpan(7, 0, 0, 0); // Cookies last 7 days
})
.AddEntityFrameworkStores<ProgramContext>();
Edit
I verified Identity is the problem. I pulled data from my repository before executing PasswordSignInAsync and it pulled the data just fine. How is the DbContext created for Identity?
It's been a long time since I posted this question, and I never shared the solution I developed, so I figured I should.
I ended up going the route of using different subdomains for my tenants. Because of this, I simply created a TenantService that checked the url and returned a connection string from config. Inside my DbContext's OnConfiguring method, I simply called the tenant service and used the returned connection string. Here is some sample code:
Tenant Service
public class Tenant
{
public string Name { get; set; }
public string Hostname { get; set; }
public string ConnectionString { get; set; }
}
public interface ITenantService
{
Tenant GetCurrentTenant();
List<Tenant> GetTenantList();
}
public class TenantService : ITenantService
{
private readonly ILogger<TenantService> _logger;
private readonly IHttpContextAccessor _httpContext;
private readonly IConfiguration _configuration;
public TenantService(
ILogger<TenantService> logger,
IHttpContextAccessor httpContext,
IConfiguration configuration)
{
_logger = logger;
_httpContext = httpContext;
_configuration = configuration;
}
/// <summary>
/// Gets the current tenant from the host.
/// </summary>
/// <returns>The tenant.</returns>
public Tenant GetCurrentTenant()
{
Tenant tenant;
var host = _httpContext.HttpContext.Request.Host;
var tenants = GetTenantList();
tenant = tenants.SingleOrDefault(t => t.Hostname == host.Value);
if (tenant == null)
{
_logger.LogCritical("Could not find tenant from host: {host}", host);
throw new ArgumentException($"Could not find tenant from host: {host}");
}
return tenant;
}
/// <summary>
/// Gets a list of tenants in configuration.
/// </summary>
/// <returns>The list of tenants.</returns>
public List<Tenant> GetTenantList()
{
var tenants = new List<Tenant>();
_configuration.GetSection("Tenants").Bind(tenants);
return tenants;
}
}
DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
if (!optionsBuilder.IsConfigured)
{
if (_tenantService == null)
{
throw new ArgumentNullException(nameof(_tenantService));
}
optionsBuilder.UseSqlServer(_tenantService.GetCurrentTenant().ConnectionString);
}
}