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.

Answer 2

We have a case similar to you. What we've done is use the implementationfactory overload of the IServiceCollection in the ConfigureServices method of the Startup class, like so:

//First register a custom made db context provider services.AddTransient<ApplicationDbContextFactory>(); //Then use implementation factory to get the one you need services.AddTransient(provider => provider.GetService<ApplicationDbContextFactory>().CreateApplicationDbContext()); 

It is very difficult for me right now to implement CreateApplicationDbContext for you, because it totally depends on what you want exactly. But once you've figured that part out how you want to do it exactly, the basics of the method should look like this anyway:

public ApplicationDbContext CreateApplicationDbContext(){   //TODO Something clever to create correct ApplicationDbContext with ConnectionString you need. }  

Once this is implemented you can inject the correct ApplicationDbContext in your controller like you did in the constructor:

public MyController(ApplicationDbContext dbContext) {     _dbContext = dbContext; } 

Or an action method in the controller:

public IActionResult([FromServices] ApplicationDbContext dbContext){ } 

However you implement the details, the trick is that the implementation factory will build your ApplicationDbContext everytime you inject it.

Tell me if you need more help implementing this solution.

Update #1 Yuriy N. asked what's the difference between AddTransient and AddDbContext, which is a valid question... And it isn't. Let me explain.

This is not relevant for the original question.

BUT... Having said that, implementing your own 'implementation factory' (which is the most important thing to note about my answer) can in this case with entity framework be a bit more tricky than what we needed.

However, with questions like these we can nowadays luckily look at the sourcecode in GitHub, so I looked up what AddDbContext does exactly. And well... That is not really difficult. These 'add' (and 'use') extension methods are nothing more than convenience methods, remember that. So you need to add all the services that AddDbContext does, plus the options. Maybe you can even reuse AddDbContext extension method, just add your own overload with an implementation factory.

So, to come back to your question. AddDbContext does some EF specific stuff. As you can see they are going to allow you to pass a lifetime in a later release (transient, singleton). AddTransient is Asp.Net Core which allows you to add any service you need. And you need an implementation factory.

Does this make it more clear?