Entity Data Models and different SQL Server versions

If you are working with Visual Studio 2008 and Entity Framework that came with .NET 3.5, then you might have noticed that your Entity Data Models (EDMs) can be quite picky about the SQL Server version you are using.

More specifically, if your development SQL Server version is 2008 and your production SQL Server version is the older 2005, then you might get database errors when you deploy your application into production. There can be different error messages depending on the situation, but common ones include:

The version of SQL Server in use does not support datatype 'datetime2'.

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

Type datetime2 is not a defined system type.

That is, all these error messages seem to refer to the data type "datetime2". You can search your SQL table definitions, code and scripts high and low, but you will only find references to "datetime". What's wrong?

These error can occur if you create your data model from a SQL Server 2008 database, but use version 2005 in production. To fix the problem, you will need to manually edit the XML-based .edmx file using a suitable editor (Visual Studio will also do fine, if you right-click the .edmx file in Solution Explorer and choose Open With, and then select "XML Editor").

The file usually begins as follows:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="NorthwindModel.Store" Alias="Self"
       Provider="System.Data.SqlClient" ProviderManifestToken="2008" ...p

Notice the Schema element and its ProviderManifestToken attribute. To fix the problem, change the attribute value to "2005", rebuild your application, deploy and test. The problem should have disappeared.

The only thing to remember is that if you need to recreate your model (such as to reflect latest changes to the database structure), you will need to again edit the file manually.