Monday, December 29, 2014

A breaking change in Entity Framework 6.1.2 when using EDMX and SQL Server 2008/2005

Version 6.1.2 of Entity Framework has just been released, as noted on the ADO.NET blog. One of the new features in 6.1.2 is support for the OFFSET..FETCH SQL Server 2012+ paging syntax. This is used when you have LINQ queries with syntax similar to:

db.Albums.OrderBy(a => a.Name).Take(20).Skip(80).ToList();

The issue


But if you are using EDMX based Entity Framework development (and not Code First), this new feature can cause your application to break at runtime. This can happen under the following scenario:

You use SQL Server 2012 (or later, including LocalDb) for development, with Entity Framework 6.1.2 and generate the EDMX model based on a database hosted on that server. This sets the value of ProviderManifestToken="2012" in your EDMX file.

image

You then run the application against a SQL Server 2008 or 2005 instance (for example in test or production), and will get errors like the one reported here: http://entityframework.codeplex.com/workitem/2619:
Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.

I will consider this a breaking change, as before EF 6.1.2 the same paging SQL was generated against all SQL Server versions, but in 6.1.2, special T-SQL using the new OFFSET..FETCH syntax is used when running against SQL Server 2012 or later.

The fix

The fix is to modify your EDMX file, using the XML editor, and change the value of ProviderManifestToken from 2012 to 2008. I found that on line 7 in my EDMX file. After saving that change, the paging SQL will be generated using the “old”, SQL Server 2008 compatible syntax.

image

Whether a EF code change, which would include SQL Server version detection, is the “real” fix is debatable, as it could break expectations of a desired behaviour from the runtime.

13 comments:

Unknown said...

Just so you know, and since your post appears early in a search, this _can_ impact code first - in a very specific scenario.

I like to keep all configuration data in a database. So with this custom config provider I bootstrap an application with a connection string 'A' and retrieve application configuration from it, including the connection string to the real database (connection string 'B').

I create a dbcontext instance to 'A' and retrieve the connection string 'B', and then create a new dbcontext to query 'B' with a EF query using Take/Skip constructs, and it fails in exactly the way you describe.

After a bit of thought I realized that my database 'A' was 2012 and 'B' was 2008, matching your description of the issue - just with the difference that CodeFirst builds its assumptions based on the first database that it connected to.

Wouldnt have found this without your initial post to get me thinking - so thanks. And hopefully you can include this info about CodeFirst so the next person searching on this can benefit.

Mark Clouden

ErikEJ said...

Thanks, Mark. Are both the DcContext instances you mention the same class?

Unknown said...

I'm also having this problem. Is there still no official Fix?

ErikEJ said...

Tim: As noted in my blog post, I am not sure that an official fix is "possible"

larrybud said...

Downright terrible that the EDMX tool doesn't remember the version of the ProviderManifestToken, or that you can only access it via the XML file rather than the properties window.

Lazy Fox said...

Thanks!

Panchof said...

Thanks to you, it works for me!

Suncat2000 said...

I consider this a breaking change, too - a bug in the Entity Framework Designer in Visual Studio. Thank you for the fix. I'm sure you saved me a ton of time!

Jorge Chinchilla said...

Thanks for this , saved me a lot of time.

Ricker Silva said...

Ihave some news on this. I'm writing an Web. API project in VS 2017. It has no edmx file anymore, and the error is thrown. Am I Doomed, any way to do this programatically?

ErikEJ said...

Ricker: Yes, you can also fix it in code: https://romiller.com/2014/06/10/reducing-code-first-database-chatter/

Reman said...

He Mark Clouden: I know this is an old post, but I just had the same issue you describe. So this does affect Code First apps as well.
We have one DbContext, which can connect to different databases - if the first database is a 2012 SQL Server and another one is a 2008 SQL Server the SQL generation fails.

ferventcoder said...

The way to fix this with SQL Server 2008 and EF Core is noted at https://github.com/aspnet/EntityFrameworkCore/issues/4616#issuecomment-221331425