Damir's Corner.

Support multiple DB providers in EF Core

In most projects, EF Core is only used with a single database provider. However, I'm currently working on a project, which requires us to transition from one database provider to another. And since we can't simply stop all other development until this process is complete, we need to support two different database providers in parallel, at least temporarily.

Fortunately, most of the code can remain the same for both database providers. For example, entity classes and their attributes:

public class Person(string firstName, string lastName)
{
    public int Id { get; set; }

    [MaxLength(50)]
    public string FirstName { get; set; } = firstName;

    [MaxLength(50)]
    public string LastName { get; set; } = lastName;
}

This doesn't work if each provider requires different property values for an attribute. For example, with SQL Server, we used a Table attribute to set the schema and table name for the entity:

[Table("People", Schema = "Sample")]

This wasn't an option for MySQL because schemas are equivalent to databases, so there can't be multiple schemas in a single database. We decided to prefix the table names instead:

[Table("Sample_People")]

But of course, there can't be two different Table attributes on a single entity, one for each database provider. To work around this limitation, we decided to use the fluent API in such cases instead of data annotation attributes. This allowed us to take advantage of inheritance and have a different OnModelCreating method for each provider.

Most of the database context code remained in the common abstract base class:

public abstract class SampleContextBase(DbContextOptions options) : DbContext(options)
{
    public DbSet<Person> People { get; set; }
}

Individual database context classes for each provider derive from this base class, each implementing their own OnModelCreating method:

public class SqlServerSampleContext(DbContextOptions<SqlServerSampleContext> options)
    : SampleContextBase(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Person>().ToTable("People", "Sample");
    }
}

public class MySqlSampleContext(DbContextOptions<MySqlSampleContext> options)
    : SampleContextBase(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Person>().ToTable("Sample_People");
    }
}

For dependency injection, we register the correct implementation depending on which type of database we want the running application instance to connect to:

switch (provider)
{
    case ProviderType.SqlServer:
        services.AddDbContext<SampleContextBase, SqlServerSampleContext>(options =>
        {
            options.UseSqlServer(
                "Server=(localdb)\\mssqllocaldb;Database=EfCoreMultipleProviders;Trusted_Connection=True;MultipleActiveResultSets=true"
            );
        });
        break;
    case ProviderType.MySql:
        services.AddDbContext<SampleContextBase, MySqlSampleContext>(options =>
        {
            var serverVersion = ServerVersion.Create(8, 0, 35, ServerType.MySql);
            options.UseMySql(
                "Server=localhost;Database=EfCoreMultipleProviders;User=root;Password=root",
                serverVersion
            );
        });
        break;
}

This allows the correct context to be injected when the base class is requested:

var context = serviceProvider.GetRequiredService<SampleContextBase>();

Two different database providers also means two separate sets of migrations. The process for achieving that is well documented. Having two context classes, one for each provider, is almost everything that's needed.

When generating migrations, you now need to specify the context class to use and the output directory for the generated files:

Add-Migration InitialCreate -Context SqlServerSampleContext -OutputDir SqlServer\Migrations
Add-Migration InitialCreate -Context MySqlSampleContext -OutputDir MySql/Migrations

Similarly, the context class must be specified when running the migrations:

Update-Database -Context SqlServerSampleContext
Update-Database -Context MySqlSampleContext

That's all there is to it. After running these commands, you'll have your database set up for both database types:

A working sample project is available in my GitHub repository. The class library contains the two contexts and the migrations created as described in this post. The test demonstrates how to conditionally register the correct context class for each provider and inject it as a dependency elsewhere in code.

Although EF Core samples usually don't show how to support multiple database providers in a single project, this isn't difficult to set up. With a minimum amount of additional plumbing code, it works well. You only need to use slightly more verbose commands to generate the migrations for both providers, and to run them.