Entity Framework: Error migrating datetime fields

Using the Entity Framework with MVC3 backed by SQL Server, I need to change the name of one of my model's properties that is a DateTime type. Normally, I would just change the name, then use the console to generate a migration script and run it; however, this time the migration script throws an error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.What's going on here?


A little more research and I find that the script generated by the migration class contains the statement ALTER TABLE [Reviews] ADD [DiningDate] [datetime] NOT NULL DEFAULT '0001-01-01T00:00:00.000'.That seems all well and good, except that the span of dates that SQL Server's datetime type supports begin at January 1, 1753. Obviously, January 1, 1 is not going to be allowed as the default value in the underlying datetime field. This is the root cause of the error we're seeing.

There are a couple of ways that we can solve this issue; they both involve modifying the Up method in the generated migration class. The original generated class is shown here:

public partial class ChangeReviewCreatedToDiningDate : DbMigration
{
    public override void Up()
    {
        AddColumn("Reviews", "DiningDate", c => c.DateTime(nullable: false));
        DropColumn("Reviews", "Created");
    }
       
    public override void Down()
    {
        AddColumn("Reviews", "Created", c => c.DateTime(nullable: false));
        DropColumn("Reviews", "DiningDate");
    }
}

Solution 1 (Preferred)
The preferred way to fix this is to change the underlying field type to datetime2 which accepts a much larger range of dates (0001-01-01 through 9999-12-31). If we make this change, then we can leave the default value alone if we chose to (not specifying a default will use '0001-01-01T00:00:00.000'). This method is preferred because the datetime2 more closely matches the ISO standard, has a larger date range, a larger default fractional precision, and optional user-specified precision.

In the migration class, we'll modify the generated code for the Up method

public override void Up()
{
    AddColumn("Reviews", "DiningDate", c => c.DateTime(nullable: false, storeType: "datetime2"));
    DropColumn("Reviews", "Created");
}

This change will cause the underlying database file type to be created as datetime2 instead of datetime.

Solution 2
The other way to tackle this problem is to change the default value that the update class is using, which will in turn, change the SQL script generated to modify the database table.

Again, we'll be modifying the Up method in the generated migration class:

public override void Up()
{
    AddColumn("Reviews", "DiningDate", c => c.DateTime(nullable: false, defaultValue: new DateTime(1973, 1, 1)));
    DropColumn("Reviews", "Created");
}

This change will cause the update script to set the default value for the field to 1973-01-01T00:00:00.000, which is the minimum value that the field type will accept.

After making either of these changes, we can just re-build our project and then execute the migration again. Problem solved!