Entity Framework and SQL Server 2012 Paging

Question

SQL Server 2012 introduces a more efficient mechanism for paging using FETCH and OFFSET which could have a big impact on performance of apps which use a lot of paging. Does Entity Framework 5 support this? So if Im using EF to page using Take + Skip will the LINQ queries be translated into the new 2012 TSQL if EF is targeting SQL Server 2012?

Answer 1

As @Ladislav said, EF 5 doesn't support OFFSET & FETCH. With that said, I wanted to add a bit of perspective. I don't think it should matter much.

When you buy into an ORM like Entity Framework, you're out sourcing your query generation (for perfectly valid reasons). Whether EF uses the 'older' CTE style query with Row_Number() or the newer Fetch / Offset is an implementation detail. Microsoft could update the EF code at any point and change the query generation to use one or the other.

If you want control over the query generation, you either:

• Use EF's 'stored procedure mapping' ability
• Use stored procedures directly with EF (something I do quite often)
• write the ADO/SQL yourself, or
• use a more limited micro-orm like massive/PetaPoco

So does it matter?

Well, to a developer writing queries the new syntax is going to be a welcome relief. On the other hand, it doesn't appear that there is a real performance difference between the old CTE method and the new syntax. So from EF's perspective -- not really. We incur a significant overhead using EF, the method of paging probably won't be your break point.