c# - Dynamically change connection string in Asp.Net Core

Question

I want to change sql connection string in controller, not in ApplicationDbContext. I'm using Asp.Net Core and Entity Framework Core.

For example:

public class MyController : Controller {
    private readonly ApplicationDbContext _dbContext
    public MyController(ApplicationDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    private void ChangeConnectionString()
    {
    // So, what should be here?
    } }

How can I do this?

Answer 1

This is enough if you want to choose a connection string per http request, based on the active http request's parameters.

    using Microsoft.AspNetCore.Http;

    //..

    services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();

    services.AddDbContext<ERPContext>((serviceProvider, options) =>
        {
            var httpContext = serviceProvider.GetService<IHttpContextAccessor>().HttpContext;
            var httpRequest = httpContext.Request;
            var connection = GetConnection(httpRequest);
            options.UseSqlServer(connection);
        });

Update

A year or so later, my solution looks like bits and pieces from other answers here, so allow me to wrap it up for you.

You could add a singleton of the HttpContextAccessor on your startup file:

services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();
services.AddDbContext<ERPContext>();

This will resolve the injection on your context constructor:

public class ERPContext : DbContext
{
    private readonly HttpContext _httpContext;

    public ERPContext(DbContextOptions<ERPContext> options, IHttpContextAccessor httpContextAccessor = null)
        : base(options)
    {
        _httpContext = httpContextAccessor?.HttpContext;
    }

    //..

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var clientClaim = _httpContext?.User.Claims.Where(c => c.Type == ClaimTypes.GroupSid).Select(c => c.Value).SingleOrDefault();
            if (clientClaim == null) clientClaim = "DEBUG"; // Let's say there is no http context, like when you update-database from PMC
            optionsBuilder.UseSqlServer(RetrieveYourBeautifulClientConnection(clientClaim));
        }
    }

    //..
}

And this will give you a clean way to access and extract a claim and decide your connection.

As @JamesWilkins stated on the comments, OnConfiguring() will be called for each instance of the context that is created.

Notice the optional accessor and the !optionsBuilder.IsConfigured. You will need them to ease your tests where you would be overriding your context configuration.