EF Core - Do SQLServer migrations apply to SQLite?

Question

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://learn.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!

Answer 1

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
}

Answer 2

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.

Answer 3

public class CompositeMigrationsAnnotationsProvider : IMigrationsAnnotationProvider
{
    private readonly IMigrationsAnnotationProvider[] _providers;

    public CompositeMigrationsAnnotationsProvider(MigrationsAnnotationProviderDependencies dependencies)
    {
        _providers = new IMigrationsAnnotationProvider[] {
            new SqlServerMigrationsAnnotationProvider(dependencies),
            new SqliteMigrationsAnnotationProvider(dependencies) 
        };
    }

    public IEnumerable<IAnnotation> For(IModel model) => _providers.SelectMany(p => p.For(model));
    public IEnumerable<IAnnotation> For(IProperty property) => _providers.SelectMany(p => p.For(property));
    public IEnumerable<IAnnotation> For(IIndex index) => _providers.SelectMany(p => p.For(index));
    public IEnumerable<IAnnotation> For(IKey key) => _providers.SelectMany(p => p.For(key));
    public IEnumerable<IAnnotation> For(IForeignKey foreignKey) => _providers.SelectMany(p => p.For(foreignKey));
    public IEnumerable<IAnnotation> For(IEntityType entityType) => _providers.SelectMany(p => p.For(entityType));
    public IEnumerable<IAnnotation> For(ISequence sequence) => _providers.SelectMany(p => p.For(sequence));
    public IEnumerable<IAnnotation> For(ICheckConstraint checkConstraint) => _providers.SelectMany(p => p.For(checkConstraint)); //EF Core 3.x
    public IEnumerable<IAnnotation> ForRemove(IModel model) => _providers.SelectMany(p => p.ForRemove(model));
    public IEnumerable<IAnnotation> ForRemove(IIndex index) => _providers.SelectMany(p => p.ForRemove(index));
    public IEnumerable<IAnnotation> ForRemove(IProperty property) => _providers.SelectMany(p => p.ForRemove(property));
    public IEnumerable<IAnnotation> ForRemove(IKey key) => _providers.SelectMany(p => p.ForRemove(key));
    public IEnumerable<IAnnotation> ForRemove(IForeignKey foreignKey) => _providers.SelectMany(p => p.ForRemove(foreignKey));
    public IEnumerable<IAnnotation> ForRemove(IEntityType entityType) => _providers.SelectMany(p => p.ForRemove(entityType));
    public IEnumerable<IAnnotation> ForRemove(ISequence sequence) => _providers.SelectMany(p => p.ForRemove(sequence));
    public IEnumerable<IAnnotation> ForRemove(ICheckConstraint checkConstraint) => _providers.SelectMany(p => p.ForRemove(checkConstraint)); //EF Core 3.x
}

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

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<IMigrationsAnnotationProvider, CompositeMigrationsAnnotationsProvider>();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.UseIdentityColumns(); //EF Core 3.x
        modelBuilder.ForSqlServerUseIdentityColumns(); //EF Core 2.x
    }
}