Solved

.NET Entity Framework 4.1 Code First Problem with ProviderManifestToken

Posted on 2012-03-13
11
1,235 Views
Last Modified: 2012-04-04
My application needs to connect to two versions of SQL Server, 2005 and 2008.  Because of the differences between the datetime data type on 2008 (datetime2) and 2005 the cached entity model (built by EF 4.1 in memory) is incompatible between the two if I go through a process to connect to an instance of 2008 and then try to connect to an instance of 2005.  If I connect to 2005 and then to 2008 there are no problems.  Before you start telling me to modify the SSDL file's ProviderManifestToken value remember that the POCO (code first) pattern does not use any EDMX file types in the project.  All of that model data is generated at runtime by the entity framework and is not maintained in the project as EDMX (ssdl,csdl,msl) configuration files.  So in essence what I need is a solution or work around to this problem so as not to have to redesign my model too much.
0
Comment
Question by:retailex
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:ambience
Comment Utility
I was going to recommend to have a look at the DbModelBuilder and especially the use of DateTimePropertyConfiguration but this post would be more relevant

http://flashfm.blogspot.com/2011/11/entity-framework-41-code-first-fixing.html
0
 

Author Comment

by:retailex
Comment Utility
Thanks.  Neither of these solutions takes into account that we have TWO database types:  2005 and 2008.  Even though I am overriding the .NET DateTime type to force it to be a SQL datetime when EF connects to that 2008 database instance under the covers it maps my overridden SQL datetime to datetime2.  Then if I close that connection to the 2008 database instance and reconnect to a 2005 instance it fails because 2005 does not support the datetime2 SQL datatype.  We have to find out how to tell EF to flush its internal view and refresh either with each connection attempt (a performance hit) or just let me through my own configuration tell EF to flush its views on demand when I know that I am changing from 2008 to 2005.  Again to reiterate, if I connect to 2005 FIRST (and EF will set up its views to use datetime rather than datetime2) and then to 2008 there is no problem because the 2008 instance can handle the old datetime type.
0
 
LVL 22

Expert Comment

by:ambience
Comment Utility
Yes and the link I posted could help if you are willing to do some work in the OnModelCreating and tell EF to use the same date type for both SQL servers. Unless, ofcourse if using datetime2 is really a must, which probably does not make sense in Code First.
0
 

Author Comment

by:retailex
Comment Utility
It is no problem for me to use the same type.  But I tried a similar approach and also used the code from this site and the same problem occurs.  The same problem occurs I believe because the OnModelCreating gets only called ONCE for the context when the first connection is made.  Therefore since the model does not get rebuilt when the connection is switched back to 2005 the model still contains an internal mapping to datetime2 and it fails.
0
 
LVL 22

Expert Comment

by:ambience
Comment Utility
>> The same problem occurs I believe because the OnModelCreating gets only called ONCE for the context when the first connection is made.  Therefore since the model does not get rebuilt when the connection is switched back to 2005 the model still contains an internal mapping to datetime2 and it fails.

But ... why the mapping to datetime2 exists is the question when what you are saying is that you are explicitly supplying EF the data type mapping information. If the property in question is mapped to say datetime then it should work across both servers.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 22

Expert Comment

by:ambience
Comment Utility
For example

modelBuilder.Entity<Person>().Property(s => s.CreationTime).HasColumnType("datetime");
0
 

Author Comment

by:retailex
Comment Utility
Sorry for the long response...

Using the fluent api as you have suggested has been tried with "datetime" as the type.

The only answer I can possibly come up with is that the application specified type is being overridden internally when the connection is made and never reverted back to the original specified type when the connection is dropped and another connection is made.  To me this sounds like a bug in EF 4.1 code first support but it could also be an incompatibility bug in the data provider.  I am using the System.Data.SqlClient in all cases.

Here is the exception I just received:

An error occurred while executing the command definition. See the inner exception for details. :: Inner:  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.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) Stack:     at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
   at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
   at EntityUtilityServices.ReadBaseForSpecificDateTimeRangeAndLocationReserved(PricingEntities context, String location, DateTime startRuleDateTime, DateTime endRuleDateTime) in EntityUtilityService.cs:line 715
   at EntityUtilityServices.ReadTodaysBaseForCurrentLocation(MyEntities context, String storeNumber) in EntityUtilityService.cs:line 648
   at LoadCache(MyEntities context) in InternalCore.Init.cs:line 228

This was produced as follows:

1.  Turn off primary database (a 2005 instance)
2.  Run app so it connects to the backup which is a 2008 instance.
3.  Turn on primary database (a 2005 instance)
4.  Signal app to go back to the primary...exception occurs

IF I RUN THE APP THE FOLLOWING WAY THERE ARE NO PROBLEMS:

1.  TURN ON PRIMARY DATABASE (A 2005 INSTANCE)
2.  RUN APP - EVERYTHING IS OK
3.  TURN OFF PRIMARY DATABASE (A 2005 INSTANCE)
4.  SIGNAL APP TO RELOAD DATA CAUSING IT TO CONNECT TO 2008 INSTANCE AND IT WORKS
5.  TURN ON PRIMARY DATABASE (A 2005 INSTANCE)
6.  SIGNAL APP TO RELOAD DATA CAUSING IT TO CONNECT TO THE PRIMARY AND IT WORKS - NO PROBLEMS

So I think it is safe to assume that EF or some component is caching the final datetimeX type based upon the connection it is on when it first creates the model.
0
 
LVL 22

Expert Comment

by:ambience
Comment Utility
Hmm .. Im sure I saw some nifty snippet that would dump the EDMX that EF creates for Code First so that would definitely help diagnosing what EF does. I'll post the link if I could find it.
0
 

Accepted Solution

by:
retailex earned 0 total points
Comment Utility
Final comment...there is no solution to this problem yet.  I am closing this question for now unless something radical occurs to change the situation.  I appreciate all of the help the members with ideas on this topic.
0
 
LVL 22

Expert Comment

by:ambience
Comment Utility
i think it could be that the db already exists and therefore EF syncs with already defined db types, recreating both dbs may work. But I have no evidence to support this theory and I haven't explored EF that much.
0
 

Author Closing Comment

by:retailex
Comment Utility
No adequate solution was provided by the members or myself.  It looks like no other way to end this conversation than to accept my final comment as the solution or lack of solution.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now