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.

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.

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.