drop column in Sqlite Migration

c# ef-migrations entity-framework-core migration sqlite


I use EntityFrameworkCore.Sqlite and I want use Migration everything works fine but when I want remove column, I am unable to update database. I know Sqlite has limitation.

this is my way for deal with this issue:

1.Create new Table
2.Insert oldTable data to new table
3.Drop oldTable
4.Rename new table to oldtable

protected override void Down(MigrationBuilder migrationBuilder)
    migrationBuilder.CreateTable(name: "XSettings",
       columns: table => new
           Id = table.Column<int>(nullable: false)
               .Annotation("Sqlite:Autoincrement", true),
           Name = table.Column<string>(nullable: true),
           LName = table.Column<string>(nullable: true)
       constraints: table =>
           table.PrimaryKey("PK_Settings", x => x.Id);

    migrationBuilder.Sql("ALTER TABLE Settings DROP COLUMN HName;");
    migrationBuilder.Sql("INSERT INTO XSettings * FROM Settings;");

    migrationBuilder.DropTable(name: "Settings");
    migrationBuilder.Sql("ALTER TABLE XSettings RENAME TO Settings;");

but when i run my app HName column still exist, so how can i delete a column?

12/18/2018 4:43:00 PM

Popular Answer

Use SQLiteStudio

To execute a drop column operation you must provide the row SQL code since the SQLite Limitations. Microsoft Docs: SQLite Limitations

  1. Download the SQLiteStudio from the SQLiteStudio site

  2. Load the local database file

  3. Delete the column you need and by "Commit Structure Changed" (the green ok button) the SQL statements are shown
  4. Copy the generated code for the migration and then copy it into the migrationBuilder.Sql("..."); statement or you can split it in multiple statements

Just note while copying into C#

SQLiteStudio generated SQLite Statements

3/20/2019 4:06:00 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow