asked on

.NET Entity Framework 4.1 Code First Problem with ProviderManifestToken

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.

ambience

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

retailex

ASKER

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.

ambience

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.

retailex

ASKER

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.

ambience

>> 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.

ambience

For example

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

retailex

ASKER

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.

ambience

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.

ASKER CERTIFIED SOLUTION

retailex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ambience

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.

retailex

ASKER

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.