Entity Framework Core 2.1 - Multiple Providers

A solution with only one Context (Example for SQLite + MySQL + MSSQL + PostgreSQL (or others)):

appsettings.json

{
  // Add Provider and ConnectionStrings for your EFC drivers
  // Providers: SQLite, MySQL, MSSQL, PostgreSQL, or other provider...
  "Provider": "SQLite",
  "ConnectionStrings": {
    "SQLite": "Data Source=mydatabase.db",
    "MySQL": "server=localhost;port=3306;database=mydatabase;user=root;password=root",
    "MSSQL": "Server=(localdb)\\mssqllocaldb;Database=mydatabase;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PostgreSQL": "Host=localhost;Database=mydatabase;Username=root;Password=root"
  }
}

Single DatabaseContext.cs

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

        // add Models...
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    // Check Provider and get ConnectionString
    if (Configuration["Provider"] == "SQLite")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseSqlite(Configuration.GetConnectionString("SQLite")));
    }
    else if (Configuration["Provider"] == "MySQL")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseMySql(Configuration.GetConnectionString("MySQL")));
    }
    else if (Configuration["Provider"] == "MSSQL")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("MSSQL")));
    }
    else if (Configuration["Provider"] == "PostgreSQL")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseNpgsql(Configuration.GetConnectionString("PostgreSQL")));
    }
    // Exception
    else
    { throw new ArgumentException("Not a valid database type"); }
}

Now we can do a singel migration

Add-Migration InitialCreate

Only edit every output of Add-Migration and add driver specific attributes:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
        name: "Mytable",
        columns: table => new
        {
            Id = table.Column<int>(nullable: false)
            // Add for SQLite
            .Annotation("Sqlite:Autoincrement", true)
            // Add for MySQL
            .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn)
            // Add for MSSQL
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
            // Add for PostgreSQL
            .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
            // Or other provider...
            Name = table.Column<string>(maxLength: 50, nullable: false),
            Text = table.Column<string>(maxLength: 100, nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Mytable", x => x.Id);
        });
    }

EDIT: or you use string ID "DatabaseGenerated" so you would not have to edit migrationBuilder and the add migration is multiple providers capable without ".Annotation"

EXAMPLE Model:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebApplication.Models
{
    public class Mytable
    {
        // This generate a String ID
        // No ID modification needed for providers
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public string Id { get; set; }

        // ....
    }
}

Now ready for Update-Database

Seimann's answer is good but I found working with migrations to be a pain. I wanted little or no manual work to get it working. I found the easiest way was to create a separate assembly for each provider and add an implementation of IDesignTimeDbContextFactory.

Another solution is to create a design time assembly but selecting which provider to use for migrations turned out to be difficult, at least until this feature is implemented here. I tried the suggested method of setting an environment variable before executing the migrations but I found using compiler constants to select the correct provider to be easier.

I organized this by creating a shared project to be used by all providers. Here is an example implementation that pulls your main projects configuration settings. This class will support both methods explained above so it can be simplified depending on your needs.

#if DEBUG
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
using System;
using System.IO;

namespace Database.DesignTime
{
    public class ApplicationDbContextDesignTimeFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
    {
        public ApplicationDbContext CreateDbContext(string[] args)
        {
            var configuration = new ConfigurationBuilder()
                 .SetBasePath(Path.GetFullPath(@"..\MainProjectDirectory"))
                 .AddJsonFile("appsettings.json")
                 .AddJsonFile("appsettings.Development.json")
                 .Build();

            // Determine provider from environment variable or use compiler constants below
            var databaseProvider = Environment.GetEnvironmentVariable("DatabaseProvider");

#if SQLSERVER
            databaseProvider = "SqlServer";
#endif
#if POSTGRESQL
            databaseProvider = "PostgreSql";
#endif

            var connectionString = configuration.GetConnectionString($"{databaseProvider}Connection");

            var contextBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();

            switch (databaseProvider)
            {
#if SQLSERVER
                case "SqlServer":
                    contextBuilder.UseSqlServer(connectionString, dbOptions =>
                    {
                        dbOptions.MigrationsAssembly("Database.SqlServer");
                    });
                    break;
#endif

#if POSTGRESQL
                case "PostgreSql":
                    contextBuilder.UseNpgsql(connectionString, dbOptions =>
                    {
                        dbOptions.MigrationsAssembly("Database.PostgreSql");
                    });
                    break;
#endif

                default:
                    throw new NotSupportedException(databaseProvider);
            }

            return new ApplicationDbContext(contextBuilder.Options);
        }
    }
}

#endif

Then in your database migration project add the compiler constant for each provider. For example:

Database.SqlServer.csproj <DefineConstants>SQLSERVER</DefineConstants>

Database.PostgreSql.csproj <DefineConstants>POSTGRESQL</DefineConstants>

When you want to add migrations from within VS, open the Package Manager Console and select the migration project as the Default project. When executing the command, you need to specify the project containing the implementation of IDesignTimeDbContextFactory you want to use.

Add-Migration Initial -StartupProject "Database.SqlServer"

Now you can switch back to your main project and use it as normal. Just for reference this is my relevant appsettings.json and startup code.

{
  "DatabaseProvider": "SqlServer",
  "ConnectionStrings": {
    "SqlServerConnection": "Server=(localdb)\\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PostgreSqlConnection": "Host=host;Database=DatabaseName;User ID=Test;Password=secrectPass"  
}

            services.AddDbContext<ApplicationDbContext>(options =>
            {
                switch (Configuration["DatabaseProvider"])
                {
                    case "SqlServer":
                        options.UseSqlServer(Configuration.GetConnectionString("SqlServerConnection"), dbOptions =>
                        {
                            dbOptions.MigrationsAssembly("Database.SqlServer");
                        });
                        break;

                    case "PostgreSql":
                        options.UseNpgsql(Configuration.GetConnectionString("PostgreSqlConnection"), dbOptions =>
                        {
                            dbOptions.MigrationsAssembly("Database.PostgreSql");
                        });
                        break;
                }
            });

There is another suggested way to accomplish this as explained here but I found creating derived classes means the migration will only work for instances of the derived class and not the base class. So you would need to specify the derived class type in AddDbContext. The other method mentioned requires manual work which I want to avoid.

You may want to consider a utility like AdaptiveClient. AdaptiveClient allows you to create a single DbContext with multiple provider-specific implementations of your services (MSSQL, MySQL, SQLite, etc). AdaptiveClient injects the correct implementation based on the connection string in use.

AdaptiveClient also allows you to inject transport-specific service implementations. For example many applications run both locally (same LAN as database server) and remotely (use WCF or REST). When running locally AdaptiveClient will inject an implementation of your service that talks directly to your database. This gives a ~10x performance improvement. When running remotely AdaptiveClient injects a WCF or REST implementation.

See also:

AdaptiveClient.EntityFrameworkCore

Demo Application

AdaptiveClient is available as a nuget package.

Disclaimer: I am the author of AdaptiveClient.