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:
ProviderManifestTokenfrom 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..
.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.
.edmxfile is contained in a seperate project or perhaps the entire application if you just have one MVC project. This would involve updating the
ProviderManifestTokento the appropriate version before each compilation and deploying the correct version to the client(s)/server(s).