Implicit column order is not preserved during table rebuild
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
### Actual Behavior
Given the following entities:
```C#
internal class Table1
{
public int Id { get; set; }
public int B { get; set; }
public int C { get; set; }
public int A { get; set; }
}
internal class Table2
{
public int Id { get; set; }
}
```
EF Core produces the following migration script:
```SQL
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
"MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
"ProductVersion" TEXT NOT NULL
);
BEGIN TRANSACTION;
CREATE TABLE "Table1" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Table1" PRIMARY KEY AUTOINCREMENT,
"B" INTEGER NOT NULL,
"C" INTEGER NOT NULL,
"A" INTEGER NOT NULL
);
CREATE TABLE "Table2" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Table2" PRIMARY KEY AUTOINCREMENT
);
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240224232535_FirstMigration', '8.0.2');
COMMIT;
```
All good so far. The columns are in the expected order.
Now force a table rebuild by adding an FK:
```C#
internal class Table1
{
public int Id { get; set; }
public int B { get; set; }
public int C { get; set; }
public int A { get; set; }
public int Table2Id { get; set; }
public Table2 Table2 { get; set; }
}
internal class Table2
{
public int Id { get; set; }
}
```
EF Core produces the following migration script:
```SQL
BEGIN TRANSACTION;
ALTER TABLE "Table1" ADD "Table2Id" INTEGER NOT NULL DEFAULT 0;
CREATE INDEX "IX_Table1_Table2Id" ON "Table1" ("Table2Id");
CREATE TABLE "ef_temp_Table1" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Table1" PRIMARY KEY AUTOINCREMENT,
"A" INTEGER NOT NULL,
"B" INTEGER NOT NULL,
"C" INTEGER NOT NULL,
"Table2Id" INTEGER NOT NULL,
CONSTRAINT "FK_Table1_Table2_Table2Id" FOREIGN KEY ("Table2Id") REFERENCES "Table2" ("Id") ON DELETE CASCADE
);
INSERT INTO "ef_temp_Table1" ("Id", "A", "B", "C", "Table2Id")
SELECT "Id", "A", "B", "C", "Table2Id"
FROM "Table1";
COMMIT;
PRAGMA foreign_keys = 0;
BEGIN TRANSACTION;
DROP TABLE "Table1";
ALTER TABLE "ef_temp_Table1" RENAME TO "Table1";
COMMIT;
PRAGMA foreign_keys = 1;
BEGIN TRANSACTION;
CREATE INDEX "IX_Table1_Table2Id" ON "Table1" ("Table2Id");
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240224232646_SecondMigration', '8.0.2');
COMMIT;
```
Now the columns are ordered alphabetically because the implicit column ordering wasn't preserved in the database model:
```C#
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
modelBuilder.HasAnnotation("ProductVersion", "8.0.2");
modelBuilder.Entity("Test.Table1", b =>
{
b.Property("Id")
.ValueGeneratedOnAdd()
.HasColumnType("INTEGER");
b.Property("A")
.HasColumnType("INTEGER");
b.Property("B")
.HasColumnType("INTEGER");
b.Property("C")
.HasColumnType("INTEGER");
b.Property("Table2Id")
.HasColumnType("INTEGER");
b.HasKey("Id");
b.HasIndex("Table2Id");
b.ToTable("Table1");
});
modelBuilder.Entity("Test.Table2", b =>
{
b.Property("Id")
.ValueGeneratedOnAdd()
.HasColumnType("INTEGER");
b.HasKey("Id");
b.ToTable("Table2");
});
modelBuilder.Entity("Test.Table1", b =>
{
b.HasOne("Test.Table2", "Table2")
.WithMany()
.HasForeignKey("Table2Id")
.OnDelete(DeleteBehavior.Cascade)
.IsRequired();
b.Navigation("Table2");
});
}
```
### Expected Behavior
The implicit column order is preserved in the database model, and column ordering is maintained by table rebuild.
### Provider and version information
EF Core version: 8.0.2
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 8
Contributor guide
Assessment
This issue has not been assessed yet.