Entity Framework 4.3.1 Code First: created database but no tables

Question

Today I migrated an old application from EF 4.2 to EF 4.3.1. In my application, I used CodeFirst, but after the migration, it stopped working and could not find the reason for this. To fix any other possible problem, I decided to create a small console application, and I used the data transfer walkthrough 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 the blog code, but instead of working correctly (creating a database, creating a schema and pasting a blog), I get some errors:

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

All this refers to a SQL Server 2005 expression.

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

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

I think that in both cases the problem is that EF wants to enter the first migration:

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

Apparently format c. incorrect, at least in my locale, this should be with:

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

UPDATE I tried to run it as an explicit migration and applied the migration with the -verbose flag set, and this 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 the SQL Server proxy and profiled what was going on there. I followed all the instructions one by one using the query analyzer, and the one that failed, as already mentioned above, inserts the transfer.

 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 data line from 2012-04-23T14.16.59.038Z to 2012-04-23T14:16:59.038Z command passed, so I think that somehow EF sends the date in a format that is incompatible with my locale.

Thanks. 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 the code for the DateTime field, so it works in the EN locale, but not in other locales.

To fix this problem while waiting for a formal fix, you must 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 only in manual migrations, it is enough if you need only CodeFirst, you must specify the configuration in the start code of the application or in DbContext.

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

NTN

Answer 2

I had the same problem with a completely new project, in my case I solved it by indicating the correct culture in my web.config

 <globalization enableClientBasedCulture="false" culture="en-US" />