Over the past few months, Entity Framework has decided to seemingly go renegade and ruin my days, especially with clients that have been running older versions of SQL Server (pre-2012). Without even touching Entity Framework, things just blow up.
Paging the doctor!
These scenarios generally all begin around the same way. A client gets in touch regarding some grid within their application "not working" or at least not the paging portion of it.
The initial load of one of these areas works just as expected, however when the client attempts to go to the next page... BOOM!. Nothing works, and they usually receive a tiny notification indicating that something is wrong.
Digging into the details, you try to reproduce the issue. "It works on my machine", you mutter to yourself. Only to realize that your client isn't running the latest, greatest SQL Server 2016 or whatever future build that you are on.
You pull the query out of your logs and attempt to execute it against SQL Server 2018 and then, you see the light :
Msg 102, Level 15, State 1, Line 66 Incorrect syntax near 'OFFSET'. Msg 153, Level 15, State 2, Line 66
Invalid usage of the option NEXT in the FETCH statement.
Crap. Entity Framework wants to use the nice fancy OFFSET/FETCH syntax to handle paging data, but older versions of SQL Server don't support this. This leaves you with three options :
- Convince your client to upgrade to SQL Server 2012 or higher.
- Explicitly check the version of the server that is being targeted by your Entity Framework context and manually query differently.
- Use the magic that I'm about to mention below.
Obviously the first option is great if the client is on board with it, but that often is an impractical expectation. The second option is just going to be a pain in the ass, which would eventually become obsolete once your clients move off of 2008 entirely. So that leaves us with the easiest option, updating a single attribute in your .edmx file.
Tell Entity Framework How To Do Its Job
Resolving this issue is honestly a piece of cake and simply requires just letting Entity Framework know the minimum version of SQL Server that it will be targeting. Providing this information will allow it to generate the most compatible queries to ensure messes like this don't happen.
Open up the .edmx file that corresponds to your context and look for the <Schema> element, which should appear directly below your <edmx:StorageModels> section as seen below :
<Schema Namespace="YourDataContext.Store"
Provider="System.Data.SqlClient"
ProviderManifestToken="2012"
Alias="Self"
/* other stuff */ >
The ProviderManifestToken attribute will define the version of SQL Server that you want to target, so set it to the lowest version that you want to target :
ProviderManifestToken="2008"
This will ensure that any of your paging related queries that relied on OFFSET/FETCH will be translated to their earlier equivalent queries and run just as expected.