The most common error seen when doing this is that SQL Server 2005 will complain about datetime2 not being a compatible field type. The problem is that the .edmx file for the data model was generated against SQL Server 2008 which implements the new datetime2 field, and the Entity Framework will use datetime2 fields for any DateTime .Net properties. The fix is to manually edit the .edmx file and change the ProviderManifestToken attribute of the Schema node from 2008 to 2005. This will instruct the Entity Framework to function against the 2005 compatibility level of SQL Server, using datetime fields instead of datetime2. Unfortunately, any updates made to the Model will cause the generator to recreate the .edmx file with ProviderManifestToken set to 2008.
From an old Microsoft Connect posting, it seems that the intended functionality was that the generator would use the Compatibility level of the database to assign the proper ProviderManifestToken. However, it is currently reading the SQL Server Instance Version instead and as of .Net 4.0, the Entity Framework still behaves this way. With Microsoft marking the posting as Will Not Fix and not adding in a designer setting for the property, having to remember to edit the .edmx every time a change is made to the model is not an acceptable solution.
I came up with an automated solution that uses the MS Build Community Tasks to automatically update the .edmx file in a BeforeBuild target, using the XmlUpdate task, to set the ProviderManifestToken to 2005. It took a couple tries to workout the proper Xpath expression to select the ProviderManifestToken attribute, but here is my solution:
<Import Project="$(MSBuildExtensionsPath)\MSBuildCommunityTasks\MSBuild.Community.Tasks.Targets" /> <Target Name="BeforeBuild"> <XmlUpdate Prefix="ssdl" Namespace="http://schemas.microsoft.com/ado/2009/02/edm/ssdl" XPath="//ssdl:Schema/@ProviderManifestToken" XmlFileName="Model.edmx" Value="2005"/> </Target>
Now the ProviderManifestToken is always set to 2005, no matter what database the generator uses for my model.