Entity Framework Core 2.1 - Multiple Providers

Question

What is the right way to work with multiple providers? My Example:

appsettings.json

{
  "ConnectionStrings": {
    "Sqlite": "Data Source=database.db"
  }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
     services.AddDbContext<DatabaseContext>(options =>
                    options.UseSqlite(Configuration.GetConnectionString("Sqlite")));
}

DatabaseContext.cs

public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }
    public DbSet<TestModel> TestModel{ get; set; }
}

A easy way for multiple providers?

Answer 1

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