Allow the end-user to switch the Entity Framework provider at runtime

Question

Consider that I have configured EF with a .NET Core web app:

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

I can also download a package to support for example SQLite:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(...));

How can we allow a user to "select" the provider on app install? I mean - for example, in WordPress you can choose from a dropdown.

Is this possible in .NET Core? The only way I see is to restart the app only...

Answer 1

Here is an example on how you can implement a DbContextFactory or a DbContextProxy<T> which will create the correct provider and return it.

public interface IDbContextFactory
{
    ApplicationContext Create();
}

public class DbContextFactory() : IDbContextFactory, IDisposable
{
    private ApplicationContext context;
    private bool disposing;

    public DbContextFactory()
    {
    }

    public ApplicationContext Create() 
    {
        if(this.context==null) 
        {
            // Get this value from some configuration
            string providerType = ...;
            // and the connection string for the database
            string connectionString = ...;

            var dbContextBuilder = new DbContextOptionsBuilder();
            if(providerType == "MSSQL") 
            {
                dbContextBuilder.UseSqlServer(connectionString);
            }
            else if(providerType == "Sqlite")
            {
                dbContextBuilder.UseSqlite(connectionString);
            }
            else 
            {
                throw new InvalidOperationException("Invalid providerType");
            }

            this.context = new ApplicationContext(dbContextBuilder);
        }

        return this.context;
    }

    public void Dispose(){
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing){
        if (disposing){
            disposing?.Dispose();
        }
    }
}

Also make sure you implement the disposable pattern as show above, so the context gets disposed as soon as the factory gets disposed, to prevent the DbContext remaining in memory longer than necessary and free unmanaged resources as soon as possible.

Finally register the factory as scoped, as you would the context itself:

services.AddScopedd<IDbContextFactory, DbContextFactory>();

A more advanced and generic/extendable approach is by creating a IDbContextProxy<T> class which uses a bit of reflection to get the correct constructor and the DbContextOptionsBuilder to it.

Also possible to create a IDbContextBuilder which abstracts the provider creation.

public class SqlServerDbContextBuilder IDbContextBuilder
{
    public bool CanHandle(string providerType) => providerType == "SqlServer";

    public T CreateDbContext<T>(connectionString)
    {
        T context = ... // Create the context here

        return context;
    }
}

Then you can pick the correct provider w/o a hard coded if/else or switch block just by doing

// Inject "IEnumerable<IDbContextBuilder> builders" via constructor
var providerType = "SqlServer";
var builder = builders.Where(builder => builder.CanHandle(providerType)).First();
var context = builder.CreateDbContext<ApplicationContext>(connectionString);

and adding new types of provider is as easy as adding the dependencies and an XxxDbContextBuilder class.

See here, here or here for more information about this and similar approaches.