Using Entity Framework with different version SQL Server

Question

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

I designed the database schema using SQL Server 2008 and have a table field of datetime type. the application works fine on my machine. But on the target server, the Entity Framework generates T-SQL queries for SQL Server 2008, which will cause a data type not supported exception (datetime2). The only workaround I know is to modify ProviderManifestToken in the edmx file every time 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