Solution: Error with Entity Framework – Incorrect syntax near ‘OFFSET’

Friends,

This post explains the solution to an issue that occurs with Entity Framework used against SQL Server 2008 database. The error is –

An error occurred while executing the command definition. See the inner exception for details. —> System.Data.SqlClient.SqlException: Incorrect syntax near ‘OFFSET’.

The error occurs if you are using Entity Framework EDMX file (this is to be noted that the error occurs only with Database first and not code-first approach) in your project and are taking benefit of the Take() or Skip() functions. Also, the error occurs only when the EDMX file has been generated the designer file using SQL Server 2012 but are trying to run the code against a production SQL server using 2008 or 2008 R2.

var data = db.Employees.Where(v => v.PatientID == patientID).OrderByDescending(v=>v.ID).Take(10).Skip(1).ToList();

The solution is to open the EDMX designer in a XML editor and change the value of ProviderManifestToken property from “2012” to “2008”.

Hope this small tip helps you. Cheers.