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!

I'm a bit confused, but you would just like to be able to choose what database to connect right? And this wouldn't change once the app ran? If so then would a condition before `options.UseSqlServer` suffice? e.g. `if(Environment.GetEnvironmentVariable("FIRSTENVIRONMENT") options.UseSqlServer("firstEnvironementConnectionString") else options.UseSqlServer("secondEnvironementConnectionString")` — Adrian, Mar 06 '17 at 02:31
Ugghh that 5 mins edit, anyway fixed code here. `if(Environment.GetEnvironmentVariable("FIRSTENVIRONMENT") == "environmentString") options.UseSqlServer("firstEnvironementConnectionString") else options.UseSqlServer("secondEnvironementConnectionString")` — Adrian, Mar 06 '17 at 02:37
That would set the Connection string at the application level, I need it set at the Method level. In other words, one line of code writes to database A and the next line of code reads from database B. Both use the same schema, just operating on different database. — user1142433, Mar 06 '17 at 02:39

score 36 · Accepted Answer · answered Mar 06 '17 at 04:28

36

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

}

answered Mar 06 '17 at 04:28

travis.js

5,193
1
24
21

Dangit! So obvious. I was trying to solve it through the EF API instead of just using inheritance. Thanks! – user1142433 Mar 06 '17 at 06:21
5

The above approach never works, as we need to pass the parameterized constructor for MyBloggingContext and MyBackUpBloggingContext. – Siddharth Sep 20 '19 at 02:54
Above approach works with generic T added to base context where T is the new subcontext. – Hyperdingo Jun 10 '20 at 10:10
1

@Siddharth if the parameter is DbContextOptions you can use DbContextOptions options in the Base Context an then it should not be a problem. If you need order Parameter how do you handle Dependency Injection? – sgt_S2 Jul 12 '20 at 13:51
3

@Siddharth is right. To make it work we need to do this: use only `DbContextOptions` for the base (BloggingContext) constructor (not `DbContextOptions`) and `DbContextOptions` for MyBloggingContext constructor and `DbContextOptions` for MyBackupBloggingContext constructor. Hope this helps everyone. – tala9999 Jul 15 '21 at 14:48

Hyperdingo · Answer 2 · 2022-04-25T05:20:46.623

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

}

On the public class calls , I'm getting _connection string is inaccessible due to its protection level, method must have a return type, does not contain a constructor that takes 0 arguments, and can not resolve symbol T errors — Joseph Norris, Jul 08 '21 at 21:55

score 7 · Answer 3 · answered Oct 20 '20 at 12:12

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.

score -1 · Answer 4 · answered Mar 29 '22 at 14:02

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.

Entity Framework Core multiple connection strings on same DBContext?

4 Answers4

Linked