EF7 generates wrong migrations with SQLite

Question

I try to use ASP.Net 5 with Entity Framework 7 and SQLite, and I found a strange behavior of migrator.

I taked a Visual Studio 2015 and created a new project with template "ASP.NET 5 Template Web Application".

After that I removed dependecy

"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final"

And added two dependencies and than called restore packages.

"EntityFramework.Sqlite": "7.0.0-rc1-final",
"Microsoft.Data.Sqlite": "1.0.0-rc1-final"

Like that

"dependencies": {
"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.Sqlite": "7.0.0-rc1-final",
"Microsoft.Data.Sqlite": "1.0.0-rc1-final",
"Microsoft.AspNet.Authentication.Cookies": "1.0.0-rc1-final",
"Microsoft.AspNet.Diagnostics.Entity": "7.0.0-rc1-final",
"Microsoft.AspNet.Identity.EntityFramework": "3.0.0-rc1-final",
"Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
"Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
"Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
"Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
"Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
"Microsoft.AspNet.Tooling.Razor": "1.0.0-rc1-final",
"Microsoft.Extensions.CodeGenerators.Mvc": "1.0.0-rc1-final",
"Microsoft.Extensions.Configuration.FileProviderExtensions": "1.0.0-rc1-final",
"Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
"Microsoft.Extensions.Configuration.UserSecrets": "1.0.0-rc1-final",
"Microsoft.Extensions.Logging": "1.0.0-rc1-final",
"Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
"Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final",
"Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0-rc1-final"
}

Next I went to Startup.cs and changed function ConfigurationServices like that

 public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        var dbFilePath =     Path.Combine(PlatformServices.Default.Application.ApplicationBasePath, "test.db");
        services.AddEntityFramework()
         .AddSqlite()
         .AddDbContext<ApplicationDbContext>(options =>
             options.UseSqlite("Filename=" + dbFilePath));
        System.Console.WriteLine("Use DB file:" + dbFilePath);

        services.AddIdentity<ApplicationUser, IdentityRole>()
            .AddEntityFrameworkStores<ApplicationDbContext>()
            .AddDefaultTokenProviders();

        services.AddMvc();

        // Add application services.
        services.AddTransient<IEmailSender, AuthMessageSender>();
        services.AddTransient<ISmsSender, AuthMessageSender>();
        services.AddInstance<IConfigurationRoot>(Configuration);
    }

Then I removed all files from folder "Migrations" and built the solution. All been ready to creating migrations. I ran git bash on project folder and inter a next commands:

blush@BLUSH /d/dev/testMVC
$ dnx ef migrations add firstMigration
Use DB file:D:\dev\testMVC\test.db
Done. To undo this action, use 'ef migrations remove'

and

$ dnx ef database update
Use DB file:D:\dev\testMVC\test.db
Applying migration '20160304130532_firstMigration'.
Done.

OK, I saw a database file and files in folder "Migrations" which looks like well. And I guess that the next generated migration will be empty without changes a object model. I tried to do it and enter a next command:

$ dnx ef migrations add testMigration
Use DB file:D:\dev\testMVC\test.db
Done. To undo this action, use 'ef migrations remove'

I went to see a generated migration class and I saw this code:

 protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId", table: "AspNetRoleClaims");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserClaim<string>_ApplicationUser_UserId", table: "AspNetUserClaims");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserLogin<string>_ApplicationUser_UserId", table: "AspNetUserLogins");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserRole<string>_IdentityRole_RoleId", table: "AspNetUserRoles");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserRole<string>_ApplicationUser_UserId", table: "AspNetUserRoles");
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId",
                table: "AspNetRoleClaims",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserClaim<string>_ApplicationUser_UserId",
                table: "AspNetUserClaims",
                column: "UserId",
                principalTable: "AspNetUsers",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserLogin<string>_ApplicationUser_UserId",
                table: "AspNetUserLogins",
                column: "UserId",
                principalTable: "AspNetUsers",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserRole<string>_IdentityRole_RoleId",
                table: "AspNetUserRoles",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserRole<string>_ApplicationUser_UserId",
                table: "AspNetUserRoles",
                column: "UserId",
                principalTable: "AspNetUsers",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
       ... 
       }

If I tries to apply this migration, catch the next error message:

$ dnx ef database update
Use DB file:D:\dev\testMVC\test.db
Applying migration '20160304133144_testMigration'.
System.NotSupportedException: SQLite cannot support this migration operation.
   в Microsoft.Data.Entity.Migrations.SqliteMigrationsSqlGenerator.Generate(Drop
ForeignKeyOperation operation, IModel model, RelationalCommandListBuilder builde
r)
   в Microsoft.Data.Entity.Migrations.MigrationsSqlGenerator.<>c.<.cctor>b__50_1
0(MigrationsSqlGenerator g, MigrationOperation o, IModel m, RelationalCommandLis
tBuilder b)
   в Microsoft.Data.Entity.Migrations.MigrationsSqlGenerator.Generate(MigrationO
peration operation, IModel model, RelationalCommandListBuilder builder)
   в Microsoft.Data.Entity.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyL
ist`1 operations, IModel model)
   в Microsoft.Data.Entity.Migrations.SqliteMigrationsSqlGenerator.Generate(IRea
dOnlyList`1 operations, IModel model)
   в Microsoft.Data.Entity.Migrations.Internal.Migrator.GenerateUpSql(Migration
migration)
   в Microsoft.Data.Entity.Migrations.Internal.Migrator.<>c__DisplayClass12_4.<G
etMigrationCommands>b__10()
   в Microsoft.Data.Entity.Migrations.Internal.Migrator.Migrate(String targetMig
ration)
   в Microsoft.Data.Entity.Design.MigrationsOperations.UpdateDatabase(String tar
getMigration, String contextType)
   в Microsoft.Data.Entity.Commands.Program.Executor.<>c__DisplayClass7_0.<Updat
eDatabase>b__0()
   в Microsoft.Data.Entity.Commands.Program.Executor.Execute(Action action)
SQLite cannot support this migration operation.

And that a question: what I shall to do to fix this issue?

Answer 1

This is a limitation of SQLite. See SQLite Limitations in EF.

This happens because SQLite itself does not support "ADD FOREIGN KEY". The way to work around this is to do a table rebuild. Making Other Kinds Of Table Schema Changes on sqlite.org provides instruction for how to structure a query to to do this. You'll quickly see how messy this is on SQLite and why EF does not automate this for you.

If you don't need the data in your SQLite database, you are probably better off just deleting it and using context.Database.EnsureCreated() to rebuild the tables from scratch using your new model.