Entity Framework Core multiple connection strings on same DBContext?

Question

I have an Asp.Net Core app with Entity Framework Core that I initialize as follows:

services.AddDbContext<ApplicationDbContext>(options => 
         options.UseSqlServer(sqlConnectionString));

This works fine, but I have a scenario where I need to read/write from the primary database for normal operations but for some operations I need to read from an alternate server (a replication target that's read only that we use for reporting).

With the way the new Core API does everything via Dependency Injection and configuration in StartUp.cs, how do I switch connection strings, but use the same ApplicationDbContext class?

I know that one option would be to have a duplicate of ApplicationDbContext class that I register with the DI system using a different connection string, but I'd like to avoid maintaining two identical DBContext objects just because sometimes I need to read from a different database server (but with the exact same schema).

Thanks in advance for any pointers!

Answer 1

You'll need two DbContexts.

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class MyBloggingContext : BloggingContext
{

}

public class MyBackupBloggingContext : BloggingContext
{

}

And you can register them like this:

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

    services.AddDbContext<MyBackupBloggingContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("BackupConnection")));

}

Answer 2

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")));

}

Answer 3

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.

Answer 4

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.