Fixing the Entity Framework 6 error “Incorrect syntax near ‘OFFSET’” when working with SQL Server 2012 or later

Say you have just updated your ASP.NET web application's Entity Framework components to the latest 6.1.2 version. All works fine in your development environment with SQL Server 2012. But suddenly, when you deploy your application to production, you will start to see the following error messages:

Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement.

This error happens, when the Entity Framework provider generates SQL code that is not supported by an older SQL Server version. By default, when you connect Entity Framework to a local SQL Server, the SQL code gets optimizer for this particular version. But if the actual production database is older, you might get error messages like shown above.

To fix this error, you need to manually edit your .EDMX model file. This is an XML file, and starts something like this:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="MyAppModel.Store" Provider="System.Data.SqlClient" ProviderManifestToken="2012" ... 

Here, the key to solving the issue is on the last line: the ProviderManifestToken attribute. Change this to "2008" or even "2005", and you should be good.

Happy hacking!