CODEWITHSUNDEEP
sql-server-2008sql-server-2012asp.net-mvc-5entity-framework-6edmx
John John
John John

Reputation: 1

How to avoid the "Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement."

I am working on an ASP.NET MVC 5 web application which uses Entity Framework version 6.

Now I develop the application on SQL Server 2012 inside my development server. I mapped my database tables and I generate the .edmx model file. Everything is working well on my development server.

Now when I deployed the application on our live server which uses SQL Server 2008, I got the following exception:-

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

on the following code inside my action method:-

records.Content = await db.SalesDatas.Where(x=>
                (String.IsNullOrEmpty(currentdomainfilter) || x.EmailDomain.ToLower().Contains(currentdomainfilter.ToLower().Trim()))
                &&
                (String.IsNullOrEmpty(currentgatewayfilter) || x.EmailGateway.ToLower().Contains(currentgatewayfilter.ToLower().Trim()))
                ).OrderBy(sort + " " + sortdir)
                    .Skip((page - 1) * pageSize.Value)
                    .Take(pageSize.Value).ToListAsync();

I find this link which describe the problem and how to fix it http://erikej.blogspot.ro/2014/12/a-breaking-change-in-entity-framework.html.

so I did the following inside my ASP.NET MVC project:

  1. I edit the .edmx file.
  2. I changed the value of ProviderManifestToken from 2012 to 2008

Now my application is working well on SQL Server 2012 and SQL Server 2008.

My question is whether the fix I applied is the correct way or it is a workaround. I mean is there a way I can force the .edmx to apply OFFSET..FETCH if the underlying SQL Server version is 2012, and to avoid this if the SQL Server version is 2008? The approach I followed to fix this issue (changing the value of ProviderManifestToken from 2012 to 2008), I will be eliminating any new features found in SQL Server 2012 (such as the use of OFFSET..FETCH), and have my .edmx only uses the features available in 2008..

Upvotes: 3

Views: 4912

Answers (1)

Igor
Igor

Reputation: 62228

The .edmx file content is built at compile time and this means you can't change this behavior at run time.

This means you have 2 choices.

  1. Point to the lowest Sql Server version you want to use as already pointed out by @marc_s in the comments. This is the solution you are already using and it is not a work around but just the way this works.
  2. If you wanted to support multiple Sql Server versions in your application you would have to create 1 release package per Sql Server version you want to support. A release package could be the replacement of a dll if the .edmx file is contained in a seperate project or perhaps the entire application if you just have one MVC project. This would involve updating the ProviderManifestToken to the appropriate version before each compilation and deploying the correct version to the client(s)/server(s).

Upvotes: 4

Related Questions