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 
              };

Answer 2

We've faced to same problem... Changed ProviderManifestToken="2008" to ProviderManifestToken="2005" and also changed database compatibility level to 2005.

But these did not work...

P.S. Using entity framework 4.0

---

System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentException: The version of SQL Server in use does not support datatype 'datetime2'.
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary2 identifierValues, List1 generatedValues)
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
--- End of inner exception stack trace ---
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Objects.ObjectContext.SaveChanges()
at CMSBase.Managers.NewsletterManager.Save(Newsletter newsletter) in D:\Sources\Managers\NewsletterManager.cs:line 94