LINQ to Entities produces query containing datetime2 on SQL 2005 Express

Question

I've got a LINQ to Entities app and a database project to manage the schema in a VS 2010 solution targetting .NET 4.0. The entity model is currently reverse engineered from the database. One of the tables is defined with a column of type datetime. The database project is configured to use SQL Server 2005 compatability mode and so it all deploys OK.

I've just run into an issue where an update statement via the entity framework appears to be using datetime2 rather than datetime, which causes an exception because SQL 2005 doesn't support that data type:

System.Data.UpdateException: An error occurred while updating the entries. 
See the inner exception for details. ---> System.ArgumentException: The version 
of SQL Server in use does not support datatype 'datetime2'.

From the stack trace is appears that the error seems to be occurring inside of:

System.Data.Mapping.Update.DynamicUpdateCommand

I've looked through all of my SQL code and entity code and confirmed that no references exist to datetime2 (including the dbschema file). I can only conclude that the data type is being generated in the dynamic SQL query generated by the entity framework.

How can I confirm or deny this, and how to I stop it from happening? Entity framework doesn't know that I have asked the db project to target 2005 compatability mode, and I can't see a way to point out to it the version that it's looking at. For what it's worth, I created this project on a machine that did have 2008 Express installed, but I periodically switch to another machine that doesn't (and can't upgrade just yet).

Answer 1

You need to change the ProviderManifestToken inside your EDMX to the value 2005. You probably generated your DB against a 2008 DB. Make this change and the EF will stop using 2008 syntax.