SQLite - Migration exception when DB existed before the migration - guidance

[Chris-Marassovich]

Steps to reproduce

SQLite DB - if the database already exists (created prior to efcore) the db.Database.Migrate() throws exception that tables already exist.

The issue

I built v1 of my app back in the W8.1 universal days and I do not think EF was a valid option then so I used another ORM library.
Today I am building v2 of my app using UWP and of course EFCore.
My model is simple with just three tables and no relationships defined as I manage that in code. Just PK defined and no FK's defined. The FK are there just not defined to the DB. Very simple.

I have two scenarios.
Scenario 1: New user of application and will use v2. The EFCore migration works perfectly as it is all new and my DB and tables are perfect and ready for use.

Scenario 2: User had v1 and is now upgrading to v2. The DB and tables already exist with data. When they upgrade to v2 the EFCore db.Database.Migrate() throws an exception that the tables exist.

My assumption is that the Migrate should be OK with the table existing and it does when the table was first created by the Migrate. In my case there are situations where it already existed prior to the migration and this seems to be the issue.

I really want to use the migration feature of EFCore and still keep the data when I have scenario 2.
I had a look at using db.Database.EnsureCreated() but that is still possibly not what I need.

Here is where I am looking for guidance as I do not consider the behaviour I have a bug of EFCore migrations but simply a logic issue I must handle manually.

As I see it I have two paths to a solution for my Scenario 2.
Path 1. I can capture the exception from the Migrate() and then manually alter the migrations table to make it think it worked. This means future migrations will work as expected.

Path 2. I make my v2 data model have different table names and in the Migrate() I look for the v1 tables and manually copy the data over to the new model. Note that v1 and v2 have no need to be different models.

I am looking for guidance on which one is a better solution and some examples or samples to start work from as the doco is lean at the moment and I understand why.
There could be other solutions that I am not aware and I am open to.
I think Path 2 is the better solution.
I really would like to make the upgrade from v1 to v2 seamless to the user and yet still have the use of the EFCore migrations into the future.

I would like to add what a wonderful product EFCore is and how fortunate we are to have use of it.

Regards
Chris ...

Further technical details

EF Core version: SQLite 1.0.0
EF Core Tools: 1.0.0-preview2-final
Operating system: Windows 10
Visual Studio version: 2015 Update 3
Template 10: 1.1.11

[bricelam]

This is essentially a dupe of #2167.

To inform EF that the first migration has already been applied to exiting v1 databases, you will need to create the __EFMigrationsHistory table and insert a row. Do this before calling db.Database.Migrate().

if (db.GetService<IRelationalDatabaseCreator>().Exists())
{
    db.Database.ExecuteSqlCommand(@"
        CREATE TABLE IF NOT EXISTS ""__EFMigrationsHistory"" (
            ""MigrationId"" TEXT NOT NULL CONSTRAINT ""PK___EFMigrationsHistory"" PRIMARY KEY,
            ""ProductVersion"" TEXT NOT NULL
        );

        INSERT OR IGNORE INTO ""__EFMigrationsHistory"" (""MigrationId"", ""ProductVersion"")
        VALUES ('20160627142134_InitialCreate', '1.0.0');        
    ");
}

db.Database.Migrate();