EF Migrations: Can't add DateTime column

Question

I'm using ASP.Net 4 EF 4.3.1 Code First Migrations.

I have an existing model class. I've added a property to it:

public DateTime LastUpdated { get; set; }

When I run update-database -force -verbose I get:

ALTER TABLE [MyTable] ADD [LastUpdated] [datetime] NOT NULL DEFAULT '0001-01-01T00:00:00.000'
System.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

I'm going to guess this relates to the implied default value being used in the generated SQL - it would appear to be complaining that the varchar it used to initialize things is data lost.

Kent

It's not my default - EF Code First Migrations is generating this SQL automatically for any new, not-null DateTime property/column. – Kent

The SQL DateTime datatype has a min value of 1 jan 1753. The .NET Datetime has a min value of 1 jan 0001. Unfortunately, conflicts are common. – Jarrod

Answer 1

The answer to this problem of not being able to add a non-null DateTime column is the same as the problem of getting 2 default values specified for a DateTime column: EF 4.3.1 Migration Exception - AlterColumn defaultValueSql creates same default constraint name for different tables

That is, it's been broken a long time, and you can workaround it by migrating with it nullable:

public DateTime? LastUpdated { get; set; }

PM> update-database

Then migrate again with it not null to get where you intended:

public DateTime LastUpdated { get; set; }

PM> update-database

Kent

In my case, it was necessary to remove .IsRequired() in fluent api too before to put it back. Thanks – Mispronounce

Answer 2

To make a DateTime nullable, first perform an add-migration command. then add the following code to the Up method.

migrationBuilder.DropColumn(
    name: "LastUpdated",
    table: "MyTable");

migrationBuilder.AddColumn<DateTime>(
    name: "LastUpdated",
    table: "MyTable",
    type: "datetime2",
    nullable: true,
    defaultValue: null);

Jacklight