c# - Entity Framework 核心 : default value when migrating nullable column to required

Question

我有一个 datetime2 列，它曾经是 nullable。
我们现在需要设置此列，因此我们希望根据需要进行设置并迁移默认值为 1970-1-1 的列。

我已经创建了迁移并将迁移文件编辑为以下内容:

protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<DateTime>(
            name: "Start",
            table: "Project",
            nullable: false,
            oldClrType: typeof(DateTime),
            oldType: "datetime2",
            oldNullable: true, 
            defaultValue: new DateTime(1970, 1, 1));
    }

我手动添加了 defaultValue: new DateTime(1970, 1, 1)); 行。

不幸的是，更新数据库时出现以下错误:

2021-03-22 10:12:55.5398: Error occurred in "UpdateDatabase":Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'Start', table 'dbo.Project'; column does not allow nulls. UPDATE fails.The statement has been terminated.

我也尝试通过 defaultValueSql 设置值，但出现了同样的错误:

defaultValueSql: "'1970-1-1'"

为什么 defaultValue 不起作用，是我做错了什么吗？

提前致谢

顺便说一句:这是被执行的脚本:

DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Project]') AND [c].[name] = N'Start');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Project] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Project] ALTER COLUMN [Start] datetime2 NOT NULL;
ALTER TABLE [Project] ADD DEFAULT '1970-01-01T00:00:00.0000000' FOR [Start];

Answer 1

如果你想在数据库中创建一个现有的列required，你需要确保列中没有空值。为了解决这个问题，请更新表格并用一个值填充该列。

migrationBuilder.Sql("UPDATE Project SET Start = GETDATE() WHERE Start is null");
 migrationBuilder.AlterColumn<DateTime>(
        name: "Start",
        table: "Project",
        nullable: false,
        oldClrType: typeof(DateTime),
        oldType: "datetime2",
        oldNullable: true, 
        defaultValue: new DateTime(1970, 1, 1));