EF4/5 Migrations DateTime error

[TimSylvester]

I have an application in the field using dotConnect for SQLite 3.80.350 and EF 4.3.1, using code-first models and automatic migrations.

I was looking at updating to dotConnect 4.2.96 and EF5. On initializing the database, the attempt to check whether it needs to be migrated fails with a FormatException: "String was not recognized as a valid DateTime."

On closer inspection, the __MigrationHistory table in the databases from contain entries like this:

Code: Select all

sqlite> .schema __MigrationHistory
CREATE TABLE __MigrationHistory (
  MigrationId varchar NOT NULL,
  CreatedOn datetime NOT NULL,
  Model blob NOT NULL,
  ProductVersion varchar NOT NULL,
  PRIMARY KEY (MigrationId)
);
sqlite> select * from __MigrationHistory order by MigrationId;
201209282136224_InitialCreate|2012-09-28 21:36:22|(binary data)|4.3.1
201210030249482_AutomaticMigration|2012-10-03 02:49:48.48.421|(binary data)|4.3.1
201210101927539_AutomaticMigration|2012-10-10 19:27:54.54.213|(binary data)|4.3.1

As you can see, the CreatedOn field has the seconds portion duplicated, which the .NET DateTime class is unable to handle. This looks to me like a bug in dotConnect 3.80.

If I manually edit the table to remove these duplicate fields, the migration then fails with another error: "SQLite does not support dropping a column."

Creating a fresh database, it seems that EF5 doesn't use the CreatedOn column at all:

Code: Select all

sqlite> .schema __MigrationHistory
CREATE TABLE __MigrationHistory (
  MigrationId varchar(255) NOT NULL,
  Model blob NOT NULL,
  ProductVersion varchar(32) NOT NULL,
  PRIMARY KEY (MigrationId)
);
sqlite> select * from __MigrationHistory order by MigrationId;
201210171644347_AutomaticMigration|(binary data)|5.0.0.net40

So I assume that's the column it's attempting to drop. I'm aware of this limitation of SQLite, and simply don't remove columns from my model, but I have no control over this table.

I would consider a "hack" to programmatically fix up the date columns before running the migration process, but even that isn't enough to make it work.

What can I do?

[Shalex]

Thank you for your report. The bug with removing column in the MigrationHistory table when upgrading from EF v4.3 to EF v5.0 is fixed. We will post here when the corresponding build of dotConnect for SQLite is available for download.

[TimSylvester]

Excellent, thank you. I look forward to it.

Since you only mentioned the column, will I still need to work around the date/time format issue? It seems 3.80 never actually reads those values, or reads them as strings; perhaps that will be the case in the next build as well?

[Shalex]

• The bug with inserting DateTime values in the CreatedOn column of the __MigrationHistory table for EF 4.3 is fixed
• The bug with the SQL generation of constant DateTime values is fixed

We will post here when the corresponding build of dotConnect for SQLite is available for download.

[Shalex]

New build of dotConnect for SQLite 4.2.114 is available for download. It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=29&t=25197.

[PatrickM]

I am still getting the same error as Tim when using 4.2.114 to read old data. Is there some sort of workaround flag we have to set to let the new version of Devart read the old DBs?

[Shalex]

Please update the __MigrationHistory table with your code to fix the dates which are stored there. The new (4.2.114 and higher) versions of dotConnect for SQLite insert correct dates.

[PatrickM]

Please update the __MigrationHistory table with your code to fix the dates which are stored there. The new (4.2.114 and higher) versions of dotConnect for SQLite insert correct dates.

So, will DevArt be supplying this code, since a DevArt bug cause the incorrect dates in the first place?

[PatrickM]

It's been 14 days. Any news on this?

[Shalex]

We recommend you to execute the following SQL command via our provider to fix the issue:

Code: Select all

UPDATE "__MigrationHistory"
SET "CreatedOn" = SubStr("CreatedOn", 1, IndexOf('.', "CreatedOn") + IndexOf('.', SubStr("CreatedOn", IndexOf('.', "CreatedOn") + 1)) - 1)
WHERE IndexOf('.', "CreatedOn") > 0 AND IndexOf('.', SubStr("CreatedOn", IndexOf('.', "CreatedOn") + 1)) > 0