Entity Framework Core multiple connection strings on same DBContext?

Can be done like this(tested with .net core 3.1):

public abstract partial class BloggingContext<T> : DbContext where T : DbContext
{
    private readonly string _connectionString;
    protected BloggingContext(string connectionString) { _connectionString = connectionString; }
    protected BloggingContext(DbContextOptions<T> options) : base(options) { }

    public virtual DbSet<Blog> Blogs { get; set; }
    public virtual DbSet<Post> Posts { get; set; } 

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(_connectionString);
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    ...
    }
}

public class MyBloggingContext : BloggingContext<MyBloggingContext>
{
    public MyBloggingContext(string connectionString) : base(connectionString) { }
    public MyBloggingContext(DbContextOptions<MyBloggingContext> options) : base(options) { }
}

public class MyBackupBloggingContext : BloggingContext<MyBackupBloggingContext>
{
    public MyBackupBloggingContext(string connectionString) : base(connectionString) { }
    public MyBackupBloggingContext(DbContextOptions<MyBackupBloggingContext> options) : base(options) { }
}

And in Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<MyBloggingContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    
    services.AddDbContext<MyBackupBloggingContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("BackupConnection")));

}

Connection string can be resolved using IServiceProvider. In the example below I map query parameter to configuration from appsettings.json, but you could inject any other logic you want.

services.AddDbContext<ApplicationDbContext>((services, optionsBuilder) =>
{
    var httpContextAccessor = services.GetService<IHttpContextAccessor>();
    var requestParam = httpContextAccessor.HttpContext.Request.Query["database"];

    var connStr = Configuration.GetConnectionString(requestParam);

    optionsBuilder.UseSqlServer(connStr);
});

?database=Connection1 and ?database=Connection2 in query will lead to using different connection strings. It is worth to provide default value, when parameter is missing.

It can be resolved in this way

public class AppDbContext : DbContext
{
    private string _connectionString { get; }

    public AppDbContext(string connectionString, DbContextOptions<AppDbContext> options) : base(options)
    {
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_connectionString);
    }
}

Then create the DbContext manually

var appDbContext = new AppDbContext("server=localhost;database=TestDB;Trusted_Connection=true", new DbContextOptions<AppDbContext>());

Instead of hard coding the connection, read from the connection string factory.

While the answers to this question work properly, they introduce a write-delay given that both contexts have to write data to two servers. An alternative solution is to use database replication. For instance, postgresql both has streaming and logical replication (setup article can be found here), and SQLite has LiteFS and Marmot.

Feel free to search for the replication configuration for your databse of choice, and ideally follow that solution.

First I created the DbContext and I have added 2 DbSets

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public virtual DbSet<Employee> Employees { get; set; }
    public virtual DbSet<Department> Departments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

The Models I've been created Employee

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int DepartmentId { get; set; }
    public Department Department { get; set; }
}

Department

public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }

    public virtual List<Employee> Employees { get; set; } = new();
}

Go to the controller and create an endpoint that will take connection string name that is needed to switch as a parameter then it will go and check if it exists then it will be changed from the controller

[Route("api/[controller]")]
[ApiController]
public class DbController : ControllerBase
{
    private readonly IConfiguration configuration;
    private readonly IServiceProvider serviceProvider;
    private readonly IConnectionString connectionString;

    public DbController(IConfiguration configuration, IServiceProvider serviceProvider,IConnectionString connectionString)
    {
        this.configuration = configuration;
        this.serviceProvider = serviceProvider;
        this.connectionString = connectionString;
    }
    [HttpGet("change-db")]
    public IActionResult ChangeDB(string connectionStringName)
    {
        string? conString = configuration.GetConnectionString(connectionStringName);

        // Validate the connection string
        if (string.IsNullOrEmpty(conString))
            return BadRequest("Can't find this database");

        var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        optionsBuilder.UseSqlServer(conString);

        var dbContext = new AppDbContext(optionsBuilder.Options);
        connectionString.CurrentConnectionString = dbContext is not null ? connectionStringName : string.Empty;
        
        var initialCatalog = conString.Split(';')
                                      .FirstOrDefault(part => part.StartsWith("Initial Catalog", StringComparison.OrdinalIgnoreCase))?
                                      .Split('=')[1].Trim();

        return Ok($"Switched to {initialCatalog} database.");
    }

This will return "Switched to {Database name} database." if it exists else it will return a Bad request

The IConnectionString is only used to save my connection string name to be used in another controllers The interface

public interface IConnectionString
{
    string CurrentConnectionString { get; set; }
}

The Class

public class ConnectionString : IConnectionString
{
    public string CurrentConnectionString { get; set; } = string.Empty;
}

It must be injected at the Program.cs

builder.Services.AddSingleton<IConnectionString, ConnectionString>();

The Connection string must be the default here and it will be changed after calling the endpoint

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

This code worked for me but I'm not sure if it is bad design or not.

builder.Services.AddDbContext<MyDbContext>((sp, options) =>
{
    var httpAccessor = sp.GetRequiredService<IHttpContextAccessor>();
    var connectionString = builder.Configuration.GetConnectionString("EntityConnection");
    if (httpAccessor.HttpContext.GetEndpoint().DisplayName.Contains("SomeEndpoint"))
    {
        connectionString = builder.Configuration.GetConnectionString("OtherEntityConnection");
    }
    options.UseSqlServer(connectionString,
        options => options.EnableRetryOnFailure());
    ...etc...