SqlException: Incorrect syntax near ‘OFFSET’.

If you are developing MVC application with Entity Framework using localDB database on your developer machine and then you put your product to the production server with older version of SQL Sever (2008R2 in my case) you can get exception below:

SqlException (0x80131904): Incorrect syntax near 'OFFSET'. 
Invalid usage of the option NEXT in the FETCH statement. 
Incorrect syntax near 'OFFSET'.

The issue is caused by the fact that SQL Server 2008 R2 doesn’t support SQL command OFFSET which can be called by Entity Framework.

To fix this behavior, you need to open your model .EDMX file in some XML editor and edit ProviderManifestToken from version 2012 to version 2008. Compile and it will be working again 🙂

<Schema Namespace="ComplianceRiskModel.Store" ProviderManifestToken="2012" Provider="System.Data.SqlClient" ...>
Advertisements