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

Hi Simone, what's the locale of the db machine and collation of the database? Odd that the T-SQL runs fine via SSMS though — Alex Norcliffe, Apr 19 '12 at 13:36
DB machine (my machine) is it-IT, collation of DB is French, that might explain on SQL Express (even if 2012-04-19T13.21.04.364 should be the invariant format). But SQL Compact runs in the same context of the app, that should not be the problem. — CodeClimber, Apr 19 '12 at 13:46
@CodeClimber http://stackoverflow.com/a/9745125/417747 - see if this link helps (anonther post of mine on a similar issue, following those few things should help you get thorugh, in my experience that's mostly around migrations, initializers and possibly connection string) - let me know and i'll post more thorough answer. — NSGaga-mostly-inactive, Apr 19 '12 at 13:54
I meant to use CodeFirst only with the first "initial" migration done automatically. Running as migration anyway produces the same result: I get the same error "The format of the specified date or time datepart is not valid. [ String = 2012-04-19T13.56.45.437 ]" — CodeClimber, Apr 19 '12 at 13:58
Was the date generated by migrations? I believe the valid format is '2012-04-19T13:21:04.364' not '2012-04-19T13.21.04.364' (colons vs. dots to separate hours from minutes and minutes from seconds) — Pawel, Apr 19 '12 at 16:25
I've been trying to recreate this error on my local machine without success. Is there any chance you could send us a small solution that recreates the bug? If so, use the [Email Blog Author](http://blogs.msdn.com/b/adonet/contact.aspx) link on our team blog to let us know. — bricelam, Apr 20 '12 at 20:49
@Brice: nothing more than there is in the blog post. But I'll send you the solution on Monday. Thx — CodeClimber, Apr 21 '12 at 05:56
@Pawel you were right... manually entering the line after having changed the format of the string works. — CodeClimber, Apr 23 '12 at 14:26
@Brice added more info: basically the datetime should be 2012-04-23T14:16:59.038Z instead of 2012-04-23T14.16.59.038Z — CodeClimber, Apr 23 '12 at 14:27

score 7 · Accepted Answer · answered Apr 30 '12 at 09:53

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

score 0 · Answer 2 · answered Jul 12 '12 at 15:27

0

I had the same problem with a brand new project, in my case I solved it by specifying the right culture in my web.config

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

answered Jul 12 '12 at 15:27

JackNova

3,911
5
31
49

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

2 Answers2