Fix Sqlite Drop Column with ef core

When you do not know the recipe blog post style take a look at the introduction, or check out all recipes there are.

As usual in the recipe section we will look at the following:

• The problem to solve: Sqlite3 and ef core with alter table migrations
• Why would you do this: Sqlite3 has a limited set of alter table statements
• What do we actually do with this: find a way to circumvent this and still use the migrations feature
• How are we going to do it: Change the modelbuilder migrations file manually
• Caveats:  changing the file comes with some risk and puts more error and work on your side

In this post we will see that ef core has some specifics when it comes to a given database provider.
In this case with sqlite3. Because we are going to drop a column due to the reason that we remove a property on an entity, we will need to supply a workaround, because sqlite3 does not allow for Alter table statements.

The problem to solve

Fix Sqlite3 and ef core Drop Column statement with migrations.
When using ef core migrations to keep the database up with your changes in business entities sometimes requires to remove properties from an entity.

This is usally no problem, but in the case of sqlite3 and the provider the migrations will throw an error, because the created drop column statement is not supported by sqlite.

Why

Software is not carved into stone and requirements change while you learn more about the domain you are operating in.
This naturally leads to the change of the applications business entities. To have this incorporated in the database we use migrations in ef core.

But as mentioned in the intro, sqlite and migrations do not work in all cases of updates on the entities.
Of course we want to fix this, so we can still use sqlite3 and ef core migrations to have all the benefits that come with ef-core.

Also with this example we can show that entity framework core fails for a given provider and what we can do about it.
In this post we will see how this is implemente from a technical standpoint with Sqlite3

What

Create a situation where we have a migration that fails us when using sqlite3. Then fix it by adjusting the generated files manually.

We will use the example application that is provided on the github to this blog

First we will update an entity by adding two properties, and an attribute that puts a defaultValue on a property.
With this in place we run a migration on the application. With this migration we will then update the database, seed it and show the results.

How

In the example app add a Property to the TodoItem and remove the Date property.

public class TodoEntity
{
    public int Id { get; set; }
    public string Content { get; set; }
    public DateTime? CompletedAtDate {get;set; }
    public DateTime CreatedAtDate { get;set;}
    public bool IsDone { get; set; }
}

We now have a creation date and a completion date (which is nullable because a todo item can still be opened)

After this we create the migration:

dotnet ef migrations add AddedCreationAndCompletionTimeToTodoItem \
-c TodoItemContext -o ./Data/Migrations

From the root of our project.
The resulting Up method looks like this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn(
    name: "Date",
    table: "TodoItems");

    migrationBuilder.AddColumn<DateTime>(
    name: "CompletedAtDate",
    table: "TodoItems",
    nullable: true);

    migrationBuilder.AddColumn<DateTime>(
    name: "CreatedAtDate",
    table: "TodoItems",
    nullable: false,
    defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
}

We can see how the old property results in a drop column and the other two properties result in an add column migrationbuilder expression.

The next thing to do is to run this command from the root of the project.

dotnet ef database update

This results in the following error:

Because we use sqlite3 in this case we cannot use the DROP COLUMN instruction.
Generally this does work for migrations, but for sqlite we need to make it work by a workaround that we examine in the next steps of this post.

We could simply change it directly on the database by hand or by running a script, but this would violate the single point of truth principle that ef core helps to achieve.

Workaround to ensure single point of truth

Sqlite simply does not support all the alter table statements which is why you need to change the table in the following steps:

1. Create a new table named as the one you are trying to change
2. Copy all the data to the new table
3. Drop the old table
4. Rename the new table with the old name

This can be done with editing the migration .cs file (not the designer).
We need to change the migration file like this:

{
    migrationBuilder.CreateTable(
    name: "New_TodoItems",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        Content = table.Column<string>(nullable: true),
        CreatedAtDate = table.Column<DateTime?>(nullable:true),
        CompletedAtDate = table.Column<DateTime?>(nullable: true),
        IsDone = table.Column<bool>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_TodoItems", x => x.Id);
    });

    migrationBuilder.Sql("INSERT INTO New_TodoItems SELECT Id FROM TodoItems;");
    migrationBuilder.Sql("PRAGMA foreign_keys=\"0\"", true);
    migrationBuilder.Sql("DROP TABLE TodoItems", true);
    migrationBuilder.Sql("ALTER TABLE New_TodoItems RENAME TO TodoItems", true);
    migrationBuilder.Sql("PRAGMA foreign_keys=\"1\"", true);
    // omitted the AddColumn Statements
}

This exactly reproduces the four steps mentioned above by using the migrationBuilder.Sql method that allows to specify the sql statements by yourself.
Also it sets the PRAGMA keys. Why this is needed see here.

When we now run the command

dotnet ef database update

Everything works fine and we are good to go.

See here for more details on why this does not work.

Caveats

• With manually editing the generated files you have two issues:
  • each migration needs some manual work
  • is error prone
  • makes the files tightly coupled to the sqlite-provider
    • so make sure to have two files (one original and one with the sqlite code)