One of biggest limitations of SQLite database is that it has limited support for changing database schema after it was designed initially. For example, simple dropping of existing column is not possible.

The only schema altering commands directly supported by SQLite are the rename table, rename column and add column commands. But altough not supported other changes to the format of table is also possible by folowing these steps:

• renaming the old table
• making a new table with the old name and with necessary changes
• copy data from old to new table
• dropping the old table

All of these steps should be within a single transaction.

In .NET Core 3.1 and earlier if you would run migration and then try to update sqllite database you would get error similar to this:

However if you for example drop column , run migration and update database in .NET 5 database will be updated. Drop column is kind of operation that requires steps mentioned earlier but migration will handle that under the hood. In the folowing table you can see which database change operations are supported and which are supported in EF Core 5.0 but require creating the new table and copying the data.

If you run migration with –verbose flag you will still not see query that is executed but you can run this command: dotnet migration script --project=Your.Project nameofmigrationbeforelatest

You should see something similar to this:

BEGIN TRANSACTION;

CREATE TABLE "ef_temp_MyTable" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_MyTable" PRIMARY KEY AUTOINCREMENT,
    "DeletedAt" TEXT NULL,
    "RelatedTableId" INTEGER NOT NULL,
    "InsertedAt" TEXT NULL,
    "LocalLocationPath" TEXT NULL,
    "Md5Hash" TEXT NOT NULL,
    "UpdatedAt" TEXT NULL,
    "Version" TEXT NOT NULL,
    CONSTRAINT "FK_MyTable_RelatedTables_RelatedTableId" FOREIGN KEY ("RelatedTableId") REFERENCES "RelatedTables" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_MyTable" ("Id", "DeletedAt", "RelatedTableId", "InsertedAt", "LocalLocationPath", "Md5Hash", "UpdatedAt", "Version")
SELECT "Id", "DeletedAt", "RelatedTableId", "InsertedAt", "LocalLocationPath", "Md5Hash", "UpdatedAt", "Version"
FROM MyTable;

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;

DROP TABLE "MyTable";

ALTER TABLE "ef_temp_MyTable" RENAME TO "MyTable";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;

CREATE INDEX "IX_MyTable_RelatedTableId" ON "MyTable" ("RelatedTableId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20201126110555_testmigration', '5.0.0');

COMMIT;

BEGIN TRANSACTION;

CREATE TABLE "ef_temp_MyTable" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_MyTable" PRIMARY KEY AUTOINCREMENT,
    "DeletedAt" TEXT NULL,
    "RelatedTableId" INTEGER NOT NULL,
    "InsertedAt" TEXT NULL,
    "LocalLocationPath" TEXT NULL,
    "Md5Hash" TEXT NOT NULL,
    "UpdatedAt" TEXT NULL,
    "Version" TEXT NOT NULL,
    CONSTRAINT "FK_MyTable_RelatedTables_RelatedTableId" FOREIGN KEY ("RelatedTableId") REFERENCES "RelatedTables" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_MyTable" ("Id", "DeletedAt", "RelatedTableId", "InsertedAt", "LocalLocationPath", "Md5Hash", "UpdatedAt", "Version")
SELECT "Id", "DeletedAt", "RelatedTableId", "InsertedAt", "LocalLocationPath", "Md5Hash", "UpdatedAt", "Version"
FROM MyTable;

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;

DROP TABLE "MyTable";

ALTER TABLE "ef_temp_MyTable" RENAME TO "MyTable";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;

CREATE INDEX "IX_MyTable_RelatedTableId" ON "MyTable" ("RelatedTableId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20201126110555_testmigration', '5.0.0');

COMMIT;