Entity Framework 4.3.1 Code First: database created but tables are not

Question

Today I migrated an old application from EF 4.2 to EF 4.3.1. In my app I was using CodeFirst, but after migration it stopped working, and couldn't find a reason for that. To clear any other possible problem I decided to create a small console application and I used the Data Migration walk-through published by the ADO team:

http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-code-based-migrations-walkthrough.aspx

I copied exactly the code of the blog, but instead of working correctly (creating the DB, creating the schema, and inserting the blog) I get some errors:

• only the DB is created, but no tables
• I get this error Conversion failed when converting datetime from character string."

All of this is on SQL Server 2005 express.

I tried the same using SQL Compact, but same result (tho different error):

• only the DB is created (in this case a sdf file in the bin folder), but no tables
• I get the error The format of the specified date or time datepart is not valid. [ String = 2012-04-19T13.21.04.364 ]

I think in both cases the problem lies in the line that EF wants to enter as first migration:

INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) 
VALUES ('201204191321184_init', '2012-04-19T13.21.04.364',  ...., '4.3.1');

Apparently the format with the . is wrong, at least in my locale, it should be with :

Is this a bug or what? It always worked with other datetime before.

UPDATE I tried running it as explicit migration, and applying the migration with the -verbose flag set, and here is what I get:

PM> Update-Database -Verbose
Using NuGet project 'ConsoleApplication2'.
Using StartUp project 'ConsoleApplication2'.
Target database is: '|DataDirectory|ConsoleApplication2.ConsoleApplication1.BlogContext.sdf' (DataSource: |DataDirectory|ConsoleApplication2.ConsoleApplication1.BlogContext.sdf, Provider: System.Data.SqlServerCe.4.0, Origin: Convention).
Applying explicit migrations: [201204191356197_Initial].
Applying explicit migration: 201204191356197_Initial.
CREATE TABLE [Blogs] (
    [BlogId] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](4000),
    CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
)
CREATE TABLE [__MigrationHistory] (
    [MigrationId] [nvarchar](255) NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [Model] [image] NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
    CONSTRAINT [PK___MigrationHistory] PRIMARY KEY ([MigrationId])
)
[Inserting migration history record]
System.Data.SqlServerCe.SqlCeException (0x80004005): The format of the specified date or time datepart is not valid. [ String = 2012-04-19T13.56.45.437 ]
   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
   at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
The format of the specified date or time datepart is not valid. [ String = 2012-04-19T13.56.45.437 ]

Update 2 I installed SQL Server Profiler, and profiled what is happening over there. I executed all the statements one by one via query analyzer and the one that fails is, as already stated above, the insertion of the migration.

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

When changing the format of the datatime string from 2012-04-23T14.16.59.038Z to 2012-04-23T14:16:59.038Z the command went through, so I guess somehow EF is sending the datatime in format that is not compatible with my locale.

Thank you Simone

Answer 1

Thanks to the ADO.NET Team, this was a bug in the Migration code. Apparently they forgot to specify InvariantCulture when they generate code for a DateTime field, so it works on EN locale, but doesn't in other locales.

To fix this problem, waiting for an official fix, you should specify a custom SqlGenerator that overrides the Generate(DateTime defaultValue) method:

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

And then specify the new SqlGenerator in the Configuration class:

SetSqlGenerator("System.Data.SqlClient", new FixedSqlGenerator());

If you want to use it just in manual migrations that's enough, if you only need CodeFirst, you have to specify the configuration in the application startup code or in the DbContext.

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

HTH