Entity Framework Core with SQLite Migration Limitations

This is part of what has turned into a series on Entity Framework Core with SQLite. The other parts can be found below.

Entity Framework Core with SQLite
Entity Framework Core Errors Using Add-Migration
Entity Framework Core with SQLite Scaffolding

The starting point of the code for this post can be found here.

Migration Limitations when using SQLite

SQLite’s ALTER TABLE is limited which in turn limits what Entity Framework Core can do via a migration. The official docs on the subject can be found here. These limitations are on the Entity Framework Team’s list of issues as an open enhancement and can be tracked here.

As long as you are just adding new tables or columns you would never notice the limitation, but if you have spelling problems like I do then the need to rename a column can be important. Thankfully things like ReSpeller (link is to the pro page, but a free version is available in ReSharpers extension manager) help with my spelling issues.

Unsupported example with a column rename

As an example of how to handle a migration that isn’t supported, we are going to rename the State property of the Contact class to Subregion.

Rename property on the model

Open the Contact class which can be found in the Models directory and make the following change.

Before:
public string State { get; set; }

After:
public string Subregion { get; set; }
Add a migration

With the property name change using the following command in the Package Manager Console to create a new migration.

Add-Migration RenameContactStateToSubregion -c ContactsDbContext

Which produces the following migration class.

public partial class RenameContactStateToSubregion : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "State",
            table: "Contacts",
            newName: "Subregion");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "Subregion",
            table: "Contacts",
            newName: "State");
    }
}
Error trying to apply the migration

As expected when an attempt to apply the above migration results in the following exception.

System.NotSupportedException: SQLite does not support this migration operation (‘RenameColumnOperation’). For more information, see http://go.microsoft.com/fwlink/?LinkId=723262.

Modify migration to manually rename the column

Searching for how to rename a column in SQLite will turn up a lot of results including this from the official docs and answers like this on StackOverflow. The gist of the how to do a rename is to create a new table with the desired schema, copy the data from the original table, drop the old table, and finally rename the new table to match the original name.

Now knowing the process the migration above can be modified to apply SQL directly instead of using Entity Framework Core to generate the SQL. This can be done by using the Sql function of the MigrationBuilder class. The following is the resulting migration.

public partial class RenameContactStateToSubregion : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            @"PRAGMA foreign_keys = 0;

              CREATE TABLE Contacts_temp AS SELECT *
                                            FROM Contacts;
              
              DROP TABLE Contacts;
              
              CREATE TABLE Contacts (
                  Id         INTEGER NOT NULL
                                     CONSTRAINT PK_Contacts PRIMARY KEY AUTOINCREMENT,
                  Address    TEXT,
                  City       TEXT,
                  Email      TEXT,
                  Name       TEXT,
                  Phone      TEXT,
                  PostalCode TEXT,
                  Subregion  TEXT
              );
              
              INSERT INTO Contacts 
              (
                  Id,
                  Address,
                  City,
                  Email,
                  Name,
                  Phone,
                  PostalCode,
                  Subregion
              )
              SELECT Id,
                     Address,
                     City,
                     Email,
                     Name,
                     Phone,
                     PostalCode,
                     State
              FROM Contacts_temp;
              
              DROP TABLE Contacts_temp;
              
              PRAGMA foreign_keys = 1;");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
    }
}

You will notice that I didn’t bother doing the Down function, but the same idea would apply when trying to undo a migration. SQLiteStudio or similar tools can be used to generate the SQL above if SQL isn’t something you want to deal with.

Fix other references to the renamed field

This isn’t really the topic of this post, but I wanted to throw in a reminder that after a rename like this there are places that will need to be updated that the tooling may not have picked up. For example, make sure all your views are using the new column as well as any bind statements in your controllers.

Wrapping up

The first time I hit the need to rename a column and it resulted in an exception it was extremely frustrating. Over time as I learned what the tooling around SQLite provides it has become less of an issue. I look forward to seeing what the Entity Framework team does in the future around this issue. The finished code can be found here.

5 thoughts on “Entity Framework Core with SQLite Migration Limitations”

    1. Open the table you would like to change and make your change and when you got to apply them you should see an option to see the SQL. Not near a computer so I can’t verify. If you still have issues let me know and I will get more information.

  1. Thank for your post. I had the same problem and at the first search your post showed up. But I had a problem with your method. Because of having real data in my DB, my code did not work with the error message “Foreign key failed”. After some research, I came to the answer that “PRAGMA foreign_keys” does not work in the transaction that migrationBuilder.Sql automatically puts statements in. To overcome this issue, I simply separated those pragma commands from main statements and came up with something like this:
    migrationBuilder.Sql(“PRAGMA foreign_keys = 0;”, true);
    migrationBuilder.Sql(sql);
    migrationBuilder.Sql(“PRAGMA foreign_keys = 1;”, true);
    Those true parameters tell Sql that it should suppress transaction. I hope this comment helps someone.

Leave a Reply to Eric Cancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.