CODEWITHSUNDEEP
sql-serverasp.net-coreentity-framework-coremariadb
Masoud Rahimi
Masoud Rahimi

Reputation: 23

How to handle multiple database type support with Entity Framework

I need to support Migration for MariaDb and SqlServer in a project. Currently, I edited migration files which created by add-migration command. You can see some part of migration file.

migrationBuilder.CreateTable(
            name: "Tbl1",
            columns: table => new
            {
                Id = table.Column<long>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1")
                    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
                Name = table.Column<string>(maxLength: 100, nullable: true),
                Measurement = table.Column<string>(maxLength: 100, nullable: true),
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Tbl1", x => x.Id);
            });

With that change, my migration files can support both database types (MariaDb and SqlServer). But, I have to set and use the same ColumnType for both. I would like to know is there any best practice provided by Entityframework to support multiple database types or not?

Upvotes: 1

Views: 1420

Answers (1)

lauxjpn
lauxjpn

Reputation: 5254

Take a look at Migrations with Multiple Providers which discusses exactly that scenario with the following two approaches:

Two migration sets

In the first approach, you generate two migrations for each model change.

One way to do this is to put each migration set in a separate assembly and manually switch the active provider (and migrations assembly) between adding the two migrations.

Another approach that makes working with the tools easier is to create a new type that derives from your DbContext and overrides the active provider. This type is used at design time when adding or applying migrations.

class MySqliteDbContext : MyDbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=my.db");
}

Note

Since each migration set uses its own DbContext types, this approach doesn't require using a separate migrations assembly.

dotnet ef migrations add InitialCreate --context MyDbContext --output-dir Migrations/SqlServerMigrations
dotnet ef migrations add InitialCreate --context MySqliteDbContext --output-dir Migrations/SqliteMigrations

One migration set

If you don't like having two sets of migrations, you can manually combine them into a single set that can be applied to both providers.

Annotations can coexist since a provider ignores any annotations that it doesn't understand. For example, a primary key column that works with both Microsoft SQL Server and SQLite might look like this.

Id = table.Column<int>(nullable: false)
    .Annotation("SqlServer:ValueGenerationStrategy",
        SqlServerValueGenerationStrategy.IdentityColumn)
    .Annotation("Sqlite:Autoincrement", true),

If operations can be applied only for one provider, or they're different between providers, use the ActiveProvider property to determine which provider is active:

if (migrationBuilder.ActiveProvider == "Microsoft.EntityFrameworkCore.SqlServer")
{
    migrationBuilder.CreateSequence(
        name: "EntityFrameworkHiLoSequence");
}

Pomelo.EntityFrameworkCore.MySql specific

Also see Implement alternatives to the current behavior to always throw, if a schema has been set for an object #982 and references about how to handle the case, that MySQL und MariaDB don't know what a SQL Server Schema (e.g. dbo) is (assuming you are using Pomelo.EntityFrameworkCore.MySql as the provider to access the MariaDB database).

Here are some code options:

// Throw an exception, if a schema is being used. This is the default.
options.UseMySql(myConnectionString, b => b.SchemaBehavior(MySqlSchemaBehavior.Throw))

// Silently ignore any schema definitions.
options.UseMySql(myConnectionString, b => b.SchemaBehavior(MySqlSchemaBehavior.Ignore))

// Use the specified translator delegate to translate from an input schema and object name to
// an output object name whenever a schema is being used.
options.UseMySql(myConnectionString, b => b.SchemaBehavior(MySqlSchemaBehavior.Translate,
    (schema, entity) => $"{schema ?? "dbo"}_{entity}"))

Upvotes: 2

Related Questions