CODEWITHSUNDEEP
.netentity-frameworkentity-framework-4
Andras Zoltan
Andras Zoltan

Reputation: 42363

EF and Multiple SQL Versions in one assembly - is it possible

This has got me a little annoyed to say the least...

I'm upgrading a service layer project to v.next and in the process 'upgrading' all my Linq to Sql models to Entity Framework. So far I've done four databases and am now running tests - my tests fail with the error

System.Data.MetadataException: Schema specified is not valid. Errors: DB.WebDB.ssdl(2,84) : error 0169: All SSDL artifacts must target the same provider. The ProviderManifestToken '2008' is different from '2005' that was encountered earlier.

Doing some googling I find that this is often caused by different dev/live sql server versions.

My scenario is different, however - I have multiple live database servers running different versions - 2005, 2008 and 2008 R2 - and I need to be able to talk to all of them, with different EDMXs.

On this SO: Multiple Versions of SQL Server using Entity Framework in a single ASP.NET application it seems that one solution is to split the EDMXs for different versions out to different assemblies (i.e. a 2005 and 2008 version). Presumably another solution will be force my ProviderManifestToken on the 2008-sourced databases to be 2005.

However, to me, the idea that I have to create different assemblies purely just to satisfy a quirk of behaviour in EF that can make one EDMX break all others is ridiculous - for me, separate assemblies are an architectural decision. Equally, hacking the EDMX file to downgrade the 2008 databases also is not desirable, especially when the next 'Update from database...' command will change it back again.

Anybody got an alternative solution?

Upvotes: 1

Views: 935

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

There is no alternate solution when using EDMX and designer. EDMX is fixed to single database implementation. In case of SQL Server it is even worse because it differs between SQL Server 2005 and 2008 dialects (we can expect 2012 coming in future). Once you are using EDMX this tight coupling with database implementation is part of SSDL (database description in EDMX). If you are also using default EF designer from VS you don't have full control over SSDL - it is always recreated when you update model from database or generated database from model.

Current solutions are:

  • Use SQL Server 2005 in development and VS designer will not overwrite your provider manifest. This configuration should work on SQL Server 2008 and 2008 R2 as well.
  • Use code first (EF 4.1 - 4.3.1) instead of EDMX where this is handled automatically at runtime
  • Don't use VS designer and maintain your EDMX manually or buy some more powerful designer
  • Create separate SSDL part for SQL Serve 2005 and 2008 and reference them correctly in connection string according to used database implementation. This has many disadvantages like maintaining two almost same SSDL documents and again you cannot use VS designer.

Upvotes: 4

Related Questions