Multiple DBContext at run time with multiple providers

Question

I have a requirement to create a dotnet core project that uses multiple database providers. One uses a SqlServer provider and the other uses a MySql provider. I do not want to have to add both DbContext and use a switch to figure out which context they need to use throughout the code. The company I'm working for is migrating from SQL Server to MySql but it will take a year to complete.

I want to figure out how to create a Generic DbContext that I can use that will return the correct DbContext based on the customer (db needed). I am doing this in dotnet core.

Trying to do something like this:

services
    .AddEntityFrameworkMySql()
    .AddEntityFrameworkSqlServer()
    .AddDbContext<GenericDbContext>();

Answer 1

You can achieve your goal by creating a factory class.

Assuming you have a DbContext class that accepts a DbContextOptions<T>:

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

We can replicate how EF Core registers and instantiates a DbContext internally in the factory and generate & supply the options dynamically.

I'm using ConnectionConfiguration as a generic placeholder for connection info. You might prefer to store this info in Customer entity.

record ConnectionConfiguration(string Provider, string ConnectionString);

class DynamicDbContextFactory<TContext> where TContext: DbContext
{
    private readonly IServiceProvider _serviceProvider;
    private Func<IServiceProvider, DbContextOptions<TContext>, TContext> _factory;

    public DynamicDbContextFactory(IServiceProvider serviceProvider, IDbContextFactorySource<TContext> factorySource)
    {
        _serviceProvider = serviceProvider;
        _factory = factorySource.Factory;
    }

    public Task<TContext> CreateDbContextAsync(ConnectionConfiguration connection)
    {
        var builder = new DbContextOptionsBuilder<TContext>()
            .UseApplicationServiceProvider(_serviceProvider);
        builder = connection.Provider switch
        {
            "sqlite" => builder.UseSqlite(connection.ConnectionString),
            "npgsql" => builder.UseNpgsql(connection.ConnectionString),
            _ => throw new InvalidOperationException("No such provider")
        };
        var db = _factory(_serviceProvider, builder.Options);
        return Task.FromResult(db);
    }
}

Here I'm using IDbContextFactorySource, which is an internal type with no support guarantee, and raises warnings during compilation. But you can simply copy its source code for finding a suitable constructor & creating a factory method.

Then we need to register a couple of services:

services.AddDbContext<AppDbContext>(db => db.UseInMemoryDatabase(nameof(AppDbContext)));
services.AddSingleton<IDbContextFactorySource<AppDbContext>, DbContextFactorySource<AppDbContext>>();
services.AddSingleton(typeof(DynamicDbContextFactory<>)); // register as open generic type and let DI close the type during resolution

Then we can resolve this factory from the service provider and create an instance on the fly:

class CustomerService
{
    private DynamicDbContextFactory<AppDbContext> _contextFactory;

    public CustomerService(DynamicDbContextFactory<AppDbContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }

    public async Task SaveThingsAsync()
    {
        // fetch connection info from somewhere
        var conn = new ConnectionConfiguration(Provider: "sqlite", ConnectionString: "Data Source=:memory:");
        await using var db = await _contextFactory.CreateDbContextAsync(conn);

        await db.Set<Sales>().AddAsync(new Sale( /*...*/));
        await db.SaveChangesAsync();
    }
}

Note: Since you're the one creating the DbContext, you should also dispose it when you're done with it (with a using statement, or by implementing IDisposable).