Dealing with SQLite EF Core Limitations - Basis Operations

Question

According to the Documentation some basic operations for Database Development using migrations aren't supported by EF Core SQLite (e.g. dropping columns, set foreign keys etc). So how to perform simple database structure changes like dropping a column without losing data and keeping the snapshot model in sync with the database (without scaffold)?

Is it worth the effort or should I just go Database-first and use scaffold-command-updates for the model? If I can't perform all necessary operations in migrations to update my database, therefore I can't use the benefit of migrations to revert my database structure. So where's the advantage of using migrations in EF-Core-Sqlite? ORM's should take the work off and not make the work more difficult.

Answer 1

Update

Sqlite Migrations: Table rebuilds is now available on EF Core 5.

Original Answer

how to perform simple database structure changes like dropping a column without losing data and keeping the snapshot model in sync with the database (without scaffold)?

The main idea is described in the EF Core Documentation : Migrations limitations workaround

You can bypass some of these limitations by manually writing code in your migrations to rebuild a table. A table rebuild involves renaming the existing table, creating a new table, copying data to the new table, and deleting the old table.

For example we have created a database with the following Blog table

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Foo { get; set; }
}

... then we want to delete Foo.

To do this, delete the Blog.Foo class property shown above.
Then add a migration to generate a Migration class.
Since MigrationBuilder.DropColumn is not supported in SQLite, we should modify the Up migration method as described in the documentation.

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Create temporary Blog table with new schema
    migrationBuilder.CreateTable(
        name: "Blog_temp_new",
        columns: table => new
        {
            BlogId = table.Column<int>(nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
            Name = table.Column<string>(nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Blog", x => x.BlogId);
        });

    // Copy Blog to Blog_temp_new
    migrationBuilder.Sql("INSERT INTO Blog_temp_new (BlogId, Name) SELECT BlogId, Name FROM Blog;");

    // Delete Blog
    migrationBuilder.DropTable("Blog");

    // Rename Blog_temp_new to Blog
    migrationBuilder.RenameTable("Blog_temp_new", newName: "Blog");
}

All the Blog data with its BlogId and Name will be preserved upon calling Database.Migrate.

I suggest you try this on a new project, with a simple single class like the Blog example. There are other things you need to do if your table have constraints or indices. But you should be able to easily learn how to deal with those if you experiment in a simple sandbox, rather than trying to fix it on your main project.

Is it worth the effort

Based from my experience, Yes! I find Model-First easier to work with compared to Database-First. I prefer doing everything in C# as much as possible but if you're a SQL expert then maybe you would have a different view than mine. :)