Entity Framework uses incompatible types (e.g. datetime2) when deployed to systems with older SQL Server versions, even when source database had compatibility level set correctly - by Code Chief

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


28
0
Sign in
to vote
ID 345018 Comments
Status Closed Workarounds
Type Bug Repros 21
Opened 5/20/2008 2:58:36 AM
Access Restriction Public

Description

I have VS 2008 SP1 Beta & .NET 3.5 SP1 Beta (latest Entity Framework) with SQL 2008 Feb CTP. One of the solutions I am creating is for SQL 2005, one is for SQL 2000. The model databases for these solutions both have the database compatibility level set correctly in SQL Server on my developer machine.

When deploying the SQL 2005 solution (a C# 3.5 windows service which queries the database with Entity Framework) an exception is thrown during the Entity Framework's internal command execution generator which states that "datetime2" is not supported on the server version.

This is incorrect because the database model was indeed compatible and set correctly to use only SQL 2005 (or 2000 in the second solution) types. Also, even if it were a 2008 database, there was no datetime2 in the commands or views which were being queried so why was it trying to use that type?

I discovered the workaround was to edit the model in the VS XML Editor and manually change the ProviderManifestToken from 2008 to 2005 or 2000 according to the desired SQL compatibility level.

I would suggest this is a bug because of:-
1) The default setting should be taken from the source database compaibility level, not the server version of the connection.
2) This attribute should be exposed somewhere visibly in the designer. It is a core setting which affects the deployability of the solution, and developers need to take care.
3) It should not have tried to use datetime2 in it's internally generated code for a schema which only had datetime.

I feel it is normal for a professional developer to have a newer SQL version installed on his work machine, and to create solutions which must support different older SQL server versions.

The most important fix is #2, and I guess this should be a simple fix to simply expose the setting in the designer? I would guess #1 is also straightforward, just query the database comapibility level instead of the server level. Also, perhaps you should prompt the developer during the wizard, which would help communicate the importance of this setting. I would suggest the setting goes into the root of the edmx when open in the designer (i.e. when you click the background of the diagram).
Sign in to post a comment.
Posted by William Shaw on 8/7/2012 at 10:10 PM
Now I got the same issue..
Posted by pdubs1087 on 7/23/2012 at 9:20 AM
It's unacceptable that this apparently won't be fixed. EF is ignoring the DB compatibility level.
Posted by Joe Farlow on 6/12/2012 at 12:20 PM
This makes absolutely no sense. Entity Framework should not be casting anything to datetime2 unless there is a datetime2 field involved with one of the entities. The compatibility level shouldn't matter. If this is by design, it is an extremely poor one.
Posted by Mordy on 2/7/2012 at 2:36 AM
Still waiting for that visible setting.
Posted by Johannes Herbst on 4/13/2011 at 5:31 AM
Still the same behaviour in .Net 4.0!
If you update your modell from an 2008 database connection and you forgot to manually change back the providermanifesttoken to 2005 you have later on troubles to run the application together with SQL Server 2005 (it is in fact impossible!).

Please fix this issue as it is really a stupid "bug" (I hope it is not a feature) :-(

Posted by blast461 on 7/8/2010 at 9:31 AM
This is a problem in a team with multiple developers and different SQL versions installed. Please fix this. It is adding step that should not be required, when it can be simply configured in the property editor.
Posted by cseg on 11/17/2009 at 1:00 AM
Using EF 4.0 and recreating the database from model EF generated SQL use DateTime and not DateTime2, I'm using SQL 2008 in both development and server machines. The ProviderManifestToken is "2008", I think that the generated SQL should use DateTime2 instead DateTime.

Posted by EricAES on 11/6/2009 at 7:29 AM
It appears that this is still not addressed for VS2010. I'm developing with SQL Server 2008, and targeting SQL Server 2005 and above. I don't want to have to remember to change the xml manually every time I make a change to the model while I'm developing a large new application. Many of my internal testers are mostly using SQL Server 2005, and if I forget to change this, they tell me the program's broken.

Please add a property that we can set once and it will be remembered.
Posted by spider7410 on 11/5/2009 at 5:42 AM
The work around is unacceptable and would not be a valid work around in our software product.
Posted by Mike Hardy on 6/24/2009 at 4:53 PM
Has this been fixed yet? I still don't see a property. Having to hand edit the xml every time I want to regen is completely unacceptable.
Posted by Stewart Armbrecht on 2/12/2009 at 2:21 PM
I agree with code chief. This is a major annoyance.
Posted by Code Chief on 9/18/2008 at 12:52 AM
Why don't you want to fix this?

It is absolutely unacceptable that a Visual Studio developer is forced to generate code that only works with the version of SQL server installed on his development PC. Many ISVs and even in-house developers in enterprises in the middle of an upgrade or maintaining older systems will have to write code compatible with more than one version of SQL Server.

All this needs is a simple designer property to override or the logic I recommended (use the database compatibility level from the connection or a query during refresh). That is not much work and there is a great benefit.

Right now every time my model changes I must remember to go and edit the XML else I get a build that won't work on some servers. This is also a maintainability issue because later other developers won't be aware of this and making a simple update will result in a failing system, worse because the unit tests will pass on the development machine but fail in integration with strange errors. This will surely cost a great deal of time.

Please think again about making this VERY simple code change.
Posted by zac morris on 9/8/2008 at 2:22 PM
This issue still exists in RTM version of Entity Framework. There is still no designer visibility for SQL compatibility.
Posted by Code Chief on 5/23/2008 at 2:14 AM
Great thanks. If the setting was visible in the designer that would make make the default value not such a problem.
Posted by Microsoft on 5/21/2008 at 5:06 PM
Thank you for reporting this issue. To answer your questions:


1)    The default setting should be taken from the source database compaibility level, not the server version of the connection.

The behavior that you are seeing is by design. The Entity Framework targets the generate Transact-SQL query based on the SQL Server version that is specified in the ProviderManifestToken attribute of the Schema element in the storage model file (SSDL). This version might differ from the version of the actual SQL Server you are connected to and from its compatibility level.

2)    This attribute should be exposed somewhere visibly in the designer. It is a core setting which affects the deployability of the solution, and developers need to take care.
I’m deferring this to our tools team.

3) It should not have tried to use datetime2 in it's internally generated code for a schema which only had datetime.
This is really a consequence of (1). Given that we target the TSQL based on the SQL Server version that is specified in the ProviderManifestToken attribute, we don’t try to avoid using any types that are not used in the schema.