Using Entity Framework with different version SQL Server

Question

Hi all,

I think most of you may work with the latest version of SQL Server and deploy it to the target machine which have a relative old version on it. Like using SQL Server 2008 on production machine and SQL Server 2005 on target server. While working in such situation with Entity Framework, It made me a little bit frustrated.

I designed the database schema using SQL Server 2008, have table field of datatime type. the application works fine on my machine. But on the target server, the entity framework generate T-SQL query for SQL Server 2008, which will cause a data type not support exception(datatime2). The only walk around I know is to modify ProviderManifestToken in the edmx file everytime you update the entity model. Is there any way to do this work smarter? Thanks!

Answer 1

You must change the ProviderManifestToken attribute manually. The update wizard won't do it for you.

Answer 2

UPDATE: I wrote a tip on my blog in my Tips Series to cover this in a little more detail.

--

Well as you guessed the key to this is the ProviderManifestToken.

The EF uses this to establish what Database Types are available.

Obviously if the SSDL (Storage or Database Model) portion of the EDMX references types not supported on a particular version of SQL server you will need to modify not just the ProviderManifestToken, but also replace all the references to types not supported on the target version too.

One approach to this is to treat the SSDL portion of the file as something that can be swapped out at runtime.

To do this you need to extract the CSDL/MSL/SSDL into files rather than having them embedded in the assembly as is standard:

1. Right click on the designer canvas
2. Select Properties
3. Set 'Metadata Artifact Processing' to 'Copy to Output Directory'
4. Build
5. Now you need to copy the SSDL file (in bin\debug), and modify it to work with SQL2005, by replacing references to DateTime2 with DateTime etc.
6. Then at runtime when constructing an object context you need to manually create the ConnectionString, making it point to the correct SSDL...

And use it like this:

var connStr = @"metadata=.\model.csdl|.\modelSQL2005.ssdl|.\model.msl;
              provider=System.Data.SqlClient;
              provider connection string="
                  Data Source=.\SQLEXPRESS;
                  Initial Catalog=TipsDatabase;
                  Integrated Security=True;
                  MultipleActiveResultSets=True
              "";

using (var ctx = new MyContext(connStr))
{

}

I know this isn't ideal, but at least it gives you a workable solution.

Hope this helps

Alex

Answer 3

You could try setting the compatibility mode of your database.

That way even if you are on a SQL 2008 Server, the database is a SQL Server 2005 database.

I have not tested this, but in theory it should trick EF into thinking that it is talking to a SQL Server 2005 database.