Entity Framework Core迁移问题：非可空列改为可空列后值更新语句执行顺序异常

The issue you're facing happens because when working with SQLite (which doesn't support directly altering column nullability), EF Core translates the AlterColumn operation into a table rebuild (drop + recreate the table). By default, raw SQL operations like your UPDATE don't have dependencies on these rebuild operations, so EF Core might execute them out of order.

To force the UPDATE to run after the table rebuild completes, you can modify your extension methods to explicitly set a dependency on the auto-generated CreateTableOperation that EF Core creates for the rebuild. Here's how to adjust your code:

Modified Extension Class #

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using System.Linq;

public static class MigrationsCorrections {
    public static OperationBuilder<SqlOperation> ChangeDateToNullable(this MigrationBuilder migrationBuilder, string table, string column) {
        // Check if we're dealing with a table rebuild (common in SQLite for AlterColumn)
        var createTableOp = migrationBuilder.Operations.OfType<CreateTableOperation>()
            .LastOrDefault(op => op.Name == table);

        string sql = $"UPDATE {table} SET {column} = null WHERE {column} = '0001-01-01 00:00:00';";

        if (createTableOp != null) {
            // Create SQL operation and make it depend on the CreateTable operation
            var sqlOp = new SqlOperation { Sql = sql };
            sqlOp.DependsOn.Add(createTableOp);
            migrationBuilder.AddOperation(sqlOp);
            return new OperationBuilder<SqlOperation>(sqlOp);
        } else {
            // For databases that support direct AlterColumn (like SQL Server), run the SQL directly
            return migrationBuilder.Sql(sql);
        }
    }

    public static OperationBuilder<SqlOperation> ChangeDateToNotNullable(this MigrationBuilder migrationBuilder, string table, string column) {
        string sql = $"UPDATE {table} SET {column} = '0001-01-01 00:00:00' WHERE {column} IS null;";
        return migrationBuilder.Sql(sql);
    }
}

Key Changes Explained: #

• Dependency Management: For SQLite, we detect the auto-generated CreateTableOperation that replaces your original table after the AlterColumn. By adding this operation as a dependency to our UPDATE SQL operation, EF Core ensures the table is fully recreated before running the update.
• Database Agnostic: The code checks if a table rebuild is in progress. For databases like SQL Server that support direct column modification, it falls back to the original raw SQL execution since no rebuild is needed.

Your Migration Class (No Changes Needed) #

Your existing migration class can stay the same—this fix is encapsulated in the extension method:

public partial class MetaPersonBirthDateNullable : Migration {
    protected override void Up(MigrationBuilder migrationBuilder) {
        migrationBuilder.AlterColumn<DateTime>(
            name: "BirthDay",
            table: "MetaPersons",
            type: "TEXT",
            nullable: true,
            oldClrType: typeof(DateTime),
            oldType: "TEXT");

        migrationBuilder.ChangeDateToNullable("MetaPersons", "BirthDay");
    }

    protected override void Down(MigrationBuilder migrationBuilder) {
        migrationBuilder.ChangeDateToNotNullable("MetaPersons", "BirthDay");
        
        migrationBuilder.AlterColumn<DateTime>(
            name: "BirthDay",
            table: "MetaPersons",
            type: "TEXT",
            nullable: false,
            defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified),
            oldClrType: typeof(DateTime),
            oldType: "TEXT",
            oldNullable: true);
    }
}

Why This Works: #

EF Core's migration scheduler uses dependencies to determine execution order. By linking our UPDATE operation to the CreateTableOperation, we ensure the table is ready before we attempt to modify its data. This avoids the "pending rebuild" warning and ensures your null replacement logic runs correctly.

内容的提问来源于stack exchange，提问作者Master DJon