UpForGrabs: Support SQL Server 2012 SQL improvements (OFFSET, FETCH, etc.)


_A customer of mine is asking if it is feasible to generate SQL 2012 syntax while generating the SQL to be executed by LINQ. The primary purpose is to avoid using ROW_NUMBER() (SQL 2008 syntax) and instead use “OFFSET..FETCH” (SQL 2012 syntax).
Performance is much better using OFFSET..FETCH._
Would also have to check if there is anything more that we could/would want to support.
Closed Jul 28 at 6:50 PM by maumar
verified, closing


RoMiller wrote Mar 21, 2013 at 10:00 PM

EF Team Triage: We agree that this would be good to investigate to see what the performance would be. Taking into account where we are in the EF6 release along with the size and the impact of this feature our team is not planning to implement it in EF6. Therefore, we are moving it to the Future release to reconsider in the next release.

BriceLambson wrote Mar 25, 2013 at 8:08 PM

For a list of other features to potentially support, see Programmability Enhancements (Database Engine) for SQL Server 2012.

BriceLambson wrote May 21, 2013 at 6:45 PM

FYI, SQL Server Compact 4.0 also supports OFFSET and FETCH.

ErikEJ wrote May 21, 2013 at 7:27 PM

FYI; the SQL CE EF provider already implements OFFSET and FETCH

juvalencia wrote Nov 25, 2013 at 1:01 AM

Any updated on this? Does EF 6 supports sequences without me put code to get the sequence?

JurgenAsselman wrote Feb 10 at 1:28 PM

any update on when (or IF) OFFSET ... FETCH is going to be available in EF6?

BrandonDahler wrote Feb 17 at 6:58 AM

I have submitted a proposed implementation of this work item here: https://entityframework.codeplex.com/SourceControl/network/forks/BrandonDahler/EntityFramework/contribution/6237 .

I have also confirmed what @BriceLambson and @ErikEJ state, and used much of that implementation as a basis for this implementation.

I am not an official EF developer so I cannot tell you anything about the likelyhood/timeline of this pull request being merged and/or released.

Interestingly enough the change itself was straightforward, but supporting it with the existing tests was the tricky part.

emilcicos wrote Jun 24 at 10:30 PM

Fixed in changeset 431b48ec28d1ade4b9367987a54053f34df106ac

ErikEJ wrote Jun 25 at 8:58 AM

Suggest update of release field to 6.1.2?

ErikEJ wrote Jul 28 at 7:38 PM

How about a new issue to track the other new 2012 features, like new functions etc. ?

DotNetWise wrote Sep 10 at 1:36 PM

What about supporting COUNT(*) OVER () as TotalCount along with OFFSET and FETCH ?

This way we would add a big benefit to EF6 by supporting inline counts while pagination, hence not executing the same query twice !!