Request specific connection strings in ASP.NET Core

Question

An ASP.NET Core MVC application uses virtual directory and cookie specific connection strings:

• http://example.com/database1
  • Cookie value 1
    • should use connection string: Database=db1;Search_Path=company1
  • Cookie value 2
    • should use connection string: Database=db1;Search_Path=company2
• http://example.com/database2
  • Cookie value 1
    • should use connection string: Database=db2;Search_Path=company1

This Entity Framework Core tutorial contains sample code for setting connection string for an entire application:

public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages();

    services.AddDbContext<SchoolContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("SchoolContext")));
}

How to use request specific connection strings?

Update

I tried code in answer and added the following code to Home controller:

public class HomeController 
    {
        public ActionResult Index()
        {

            using (var context = new SchoolContext(new DbContextOptions<SchoolContext>()))
            {
                var commandText = "INSERT into maksetin(maksetin) VALUES (1)";
                context.Database.ExecuteSqlRaw(commandText);
            }

This throws error

System.InvalidOperationException: 'No database provider has been configured for
 this DbContext. 
A provider can be configured by overriding the 'DbContext.OnConfiguring' method or by
  using 'AddDbContext' on the application service provider. 
If 'AddDbContext' is used, then also ensure that your DbContext type accepts a DbContextOptions<TContext> object in
 its constructor and passes it to the base constructor for DbContext.'

Breakpoint in line

  IHttpContextAccessor http = sp.GetRequiredService<IHttpContextAccessor>();

does not occur. So code in answer is not called. How to force it call ?

Answer 1

Do both of these databases have the exact same Schema?

The quick and dirty way would be to just create two different DbContext Schemas that inherit from your base Schema, and register them separately to their own connection strings in your Startup.cs

IE:

public abstract MyBaseDbContext : DbContext 
{ 
   // Setup your DB schema here, assuming both databases use the same Schema
}

public FirstDbContext : MyBaseDbContext { ... }

public SecondDbContext : MyBaseDbContext { ... }

And then in Startup:

services.AddDbContext<FirstDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("First")));
services.AddDbContext<SecondDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("Second")));

And then in controllers:

private FirstDbContext FirstDbContext { get; }
private SecondDbContext SecondDbContext { get; }

public SomeController(FirstDbContext firstDbContext, SecondDbContext secondDbContext) 
{ 
      FirstDbContext = firstDbContext;
      SecondDbContext = secondDbContext;
}


public IActionResult SomeEndpoint()
{
    // Use FirstDbContext here to do things within Database 1
}

public IActionResult SomeOtherEndpoint()
{
    // Use SecondDbContext here to do things within Database 1
}

public IActionResult AnotherEndpoint()
{
    // You can even use both DbContexts if you so choose here to 
    // perform logic involving data in both DBs!
}

If your databases dont even have the same schema, then you can just ignore the whole MyBaseDbContext thing, and just define two distinct FooDbContext classes and register both of them to their associated connection strings.