dotnet / dotnet/efcore

Implicit column order is not preserved during table rebuild

Open
#33,153 2 comments 0 reactions 0 assignees View on GitHub
area-migrations customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.