Entity Framework Core: default value when migrating nullable column to required

Question

I have a datetime2 column that used to be nullable.
We now need this column to be set so we want to set it as required and migrate the column with a default value of 1970-1-1.

I have created the migration and edited the migration-file to the following:

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));
    }

I have manually added the defaultValue: new DateTime(1970, 1, 1)); line.

Unfortunately, when updating the database I get the following error:

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.

I have also tried to set the value via defaultValueSql but the same error occurs:

defaultValueSql: "'1970-1-1'"

Why does the defaultValue not work, am I doing something wrong?

Thanks in advance

btw: this is the script that gets executed:

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];

Majid Parvin · Accepted Answer · 2021-03-22 10:01:07Z

7

If you want to make an existing column required in DB you need to make sure there is no null value in the column. in order to fix that, update the table and fill the column with a value.

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));

answered Mar 22, 2021 at 10:01

Majid Parvin

5,1806 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework Core: default value when migrating nullable column to required

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related