How to use SQL Server OFFSET & FETCH FIRST with Entity Framework 5?

Question

Is OFFSET & FETCH FIRST keywords, which are introduced in SQL Server 2012 are supported in EntityFramework 5 and Linq to SQL?

It's showing 50% performance improvement when using SELECT * FROM tables ORDER BY stime DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY instead of var a= db.table.Skip(0).Take(10);

Answer 1

Short Answer is No, it is not supported in EF5, but Version 6.1.2 of Entity Framework has just been released, as noted on the ADO.NET blog. One of the new features in 6.1.2 is support for the OFFSET..FETCH SQL Server 2012+ paging syntax.

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. Reference

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. Refrance