A few days ago I started migrating a web app I was working on from ASP.NET MVC 3 and EF4 to ASP.NET MVC 4 and EF 4.3.1. All went well, except for some problems with the automatic generation of the DB scheme using EF Code First: it was creating the database, but no tables and not even the __MigrationHistory table added in 4.3; and during the execution of the schema generation an error was raised with a weird datetime conversion error. I tried this both on SQL Express 2005 and SQL Compact and I got the same outcome, just different errors.

In SQL Express 2005 I got:

Conversion failed when converting datetime from character string.

In SQL Compact I got slightly more helpful error message:

The format of the specified date or time datepart is not valid.
[ String = 2012-04-19T13.21.04.364 ]

The cause of the problem

A bit of Googling, and I found out that this problem is related to how the __MigrationHistory is created and filled in with rows: for every “migration” a new row with, among other info, the timestamp of the migration is added.

INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion])
VALUES ('201204231416585_InitialCreate', '2012-04-23T14.16.59.038Z', ...., '4.3.1')

The problem is that, probably due to the local of my local machine (Italian) and of my test database (French), the format in which the datatime value has been serialized was wrong: instead of 2012-04-23T14.16.59.038Z it should have been 2012-04-23T14:16:59.038Z

I asked the question on StackOverflow but even there nobody was able to find a solution for the problem, but at least a guy from the ADO.NET Team looked into it and, a few days later, came out with a solution.

It’s a bug in EF

The answer is that it is a bug, as they didn’t specify InvariantCulture when they do the ToString of the data to generated the SQL script. And it will be fixed in the next version of Entity Framework. But until it comes out, and you encounter the same bug, here is how to fix it.

The workaround

First thing you have to do is specify a custom SqlServerMigrationSqlGenerator, override the Generate(DateTime) method which the one responsible for generating the value of datetime objects, and specify the InvariantCulture option and the correct colon based format.

class FixedSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override string Generate(DateTime defaultValue)
    {
        return "'" + defaultValue.ToString("yyyy-MM-ddTHH:mm:ss.fffK"
                      , CultureInfo.InvariantCulture) + "'";
    }
}

How to inject the workaround

Then you have to configure EF to use your new migration generator instead of the default one. Where to put this configuration depends on what you are using.

Using Migrations

If you are using the Migrations it’s pretty easy: just call the SetSqlGenerator method in the Configuration class for the migration.

class Configuration : DbMigrationsConfiguration<ConsoleApplication3.BlogContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
            SetSqlGenerator("System.Data.SqlClient", new FixedSqlGenerator());
        }
    }

But bear in mind that this works only if you enabled migrations.

Using Code First

If instead, like me, your are just using Code First, and just want your DB automatically created (and then updated) you’ll never go via that configuration step, so the first automatic migration will not use your new Sql generator. Another small step is needed: you have to tell EF where the Configuration is:

Database.SetInitializer(
   new MigrateDatabaseToLatestVersion<BlogContext, Migrations.Configuration>());

Where to put this line really depends on the hosting application: you can put it in the Context class, at the beginning of your console application, or in the Global.asax.cs file in a ASP.NET MVC application.

More info

If you never used EF migrations I really recommend you play around with them: it’s a really neat way to keep track of your database, even if you are not using EF as your ORM. Here are two very nice blog posts from the ADO.NET EF team that show how migrations work, but in automatic and manual manner.

• EF 4.3 Code-Based Migrations Walkthrough
• EF 4.3 Automatic Migrations Walkthrough

And finally I’d like to thank Brice from the ADO.NET EF for quickly responding to the issue and sending me the workaround.