EF Core - Do SQLServer migrations apply to SQLite?

.net-core asp.net-core entity-framework-core sqlite sql-server


I am having a simple project where I use initially SQL Server as backend with EF Core and Code-First approach (following this tutorial: https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/new-db)

Now I want to switch the implementation to SQLite. I thought I could just run the initial migration for database creation from SQL Server and apply it to SQLite. Seems it doesn't work correctly: eg. auto-increment on primary key is not applied, then I see some inconsistencies (sqlite complaining about 'int' which should be 'INTEGER'), etc.

So, does this mean that migrations are back-end dependent? If yes, shouldn't they be created in a nested folder then (eg. ./Migrations/SQLServer)?

Could anybody explain a bit more how this works?

Note: Sorry, I am new to EF Code-First and migrations... thank you!

10/5/2017 1:37:20 PM

Accepted Answer

The migrations generated by the Add-Migration/dotnet ef migrations add command are backend-specific, but you can hand-edit them to make them compatible with multiple backends. The easiest way to do it is by generating two sets of migrations as described in @alwayslearning's answer then combining them into a single migrations set.

For example, an Id column might look like this:

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

You can also conditionally do things that might be fundamentally different between backends:

if (migrationBuilder.ActiveProvider == "Microsoft.EntityFrameworkCore.SqlServer")
    // TODO
else if (migrationBuilder.ActiveProvider == "Microsoft.EntityFrameworkCore.Sqlite")
    // TODO
10/9/2017 4:55:36 PM

Popular Answer

Yes, migrations are backend dependent. Each backend has its specifics and so migrations would usually have backend specifics like column specifications.

In some ORMs it is possible to map the 'code-first' model in an abstract manner and let the backend specific providers map this abstract model to backend specific types but in any relatively complex model it's hard to keep the entire mapping backend independent.

To move to SQLite you can change the configuration in your Startup.cs where you register your DbContext to something like

services.AddDbContext<YourContext>(options => options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")))

You can then specify an explicit path for your migrations using the 'dotnet ef migrations add' command. This question has more details on setting an explicit folder path.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow