Multiple Versions of SQL Server using Entity Framework in a single ASP.NET application

Question

I am using the Entity Framework in a web application that utilizes SQL server 2000, 2005, and 2008. When I create a new EDMX file using anything other than 2008 (version of the first edmx created) I receive error 0172: All SSDL artifacts must target the same provider. The Provider 'MyDatabase' is different from ' MyDatabase ' that was encountered earlier. It seems that somewhere in the code the connection is wired to a 2008 datastore and when it checks the SSDL file and sees a different ProviderManifestToken value it throws this error. I am a little more than frustrated. It is hard to imagine that EF will only work with a single version of Sql Server per application. I am fairly sure there must be a setting or workaround. Does anyone have a solution to use different versions of SQL server and the Entity Framework within a single web application?

Answer 1

I was able to accomplish this by putting each edmx in a separate assembly. Then in the connection string, replace all the res://*/... with res://NameOfAssembly/...

I can even perform joins between the two entity models (contrary to claims I found in other sources), e.g.:

var oneDb = new Entities2000();
var otherDb = new Entities2005();

var results = from one in oneDb.SomeSet
              join other in otherDb.OtherSet
                  on one.Property equals other.Property
              select new { 
                  SomeProp = one.SomeProp,
                  OtherProp = other.OtherProp 
              };