Efcore: Multiple instances of the same DbContext with different connection strings

[nicolasr75]

I know this is more like a howto question and should be asked on StackOverflow. I did ask here:
https://stackoverflow.com/questions/45724257/asp-net-core-with-separate-databases
but it seems like there are not too many experts being able to answer this. Maybe someone here can help.

My ASP.NET Core 2.0 application must support multiple databases. Which database is to be used should depend on the ApplicationUser issuing a request on a per-request basis.

That rules out AddDbContext in Startup.cs with a single connection string (there will be multiple connection strings in my application.json file).

A multi-tenant approach as described here http://benfoster.io/blog/asp-net-5-multitenancy seems to be too restrictive because I actually don't want complete isolation. There will be users (managers etc.) that should be able to switch between databases. Normal users will be restricted to one specific database. Apart from that I thought I could manage this without third party code.

I found this
https://github.com/aspnet/EntityFrameworkCore/issues/6863
which seems to be related.

Note: I don't need multiple instances of the same context in my controller, as discussed here
https://github.com/aspnet/DependencyInjection/issues/352
I just need the correct one depending on the user.

In my SO post 'poke' comments:

Database contexts are usually configured to be created once per every request, so the configuration can change on a per-request basis. You just need to come up with a nice way to configure the contexts depending on your user ... So you should use a single database context but just make sure that your DbContextOptions configure the correct connection string per request. Or put the logic inside the db context (ugh though) by overriding OnConfiguring

So how do I use DbContextOptions to realize this?

I would be thankful for every hint!

[ajcvickers]

@nicolasr75 Assuming you are using EF Core 2.0, then you can put logic into the code that builds the options. For example:
C# services.AddDbContext<MyDbContext>((serviceProvider, builder) => { // Use some code here to look up the connection string // Note that the service provider (serviceProvider) is available if needed var connectionString = ...; builder.UseSqlServer(connectionString); });
The options are now registered as scoped by default, so this code will run once each request to determine the connection string to use.

You could also put similar code into OnConfiguring if you prefer that pattern.

[ste4net]

I use a similar aproach with ef core 1.x but tenant are separated by db schema.Same db different schemas. Shared tables like users groups etc... are in dbo schema.
Login use a context with only shared tables. After login the context is created with correct schema taken from user profile.
I wrote something about it here: #9965
If you need different connections you can use a separate database for login tables (and user connection configuration ) and a database for each customer (like others suggested to you). You will have to find a solution for shared tables (like a replication from login database).

[eduardomar]

@ajcvickers There way to get a route attribute([Route("api/{CompanyCode}/[controller]")]) in this code?

[ajcvickers]

@eduardomar That's an ASP.NET question, rather than an EF one. I don't know the answer.

[Peter-Optiway]

I managed to refactor out some smelly code using the solution provided here, i combined it with AsyncLocal.

c# public class DatabaseContext : IDatabaseContext { AsyncLocal<bool> asyncLocal = new AsyncLocal<bool>(); public bool IsMaintenance { get => asyncLocal.Value; set => asyncLocal.Value = value; } }

Then i can set use databaseContext.IsMaintenance = true; to set the value in a given AsyncContext, and in the IsMaintenance flag by using the serviceProvider in the AddDbContextPool callback function.