c# - Why does System.Data.Linq generates ROW_NUMBER() for Paging instead of OFFSET/FETCH for SQL Server 2012

Question

We are using Linq and Entity Framework to access a SQL Server 2012 database. We are having some performance issue, so after some investigation, we were able to fix some of the problems, but I would like to use SQL query with OFFSET/FETCH instead of ROW_NUMBER() and BETWEEN syntax.

The performance difference is not so big. OFFSET/FETCH is quicker by about 10%. Do you have any idea why the generated query uses ROW_NUMBER() and BETWEEN syntax? What can I do to force Linq to generate OFFSET/FETCH query?

C# code:

var orders = dc.Orders.OrderBy(q => q.LastModifiedTimestamp)
                      .Skip(q => skipCount)
                      .Take(q => takeCount)
                      .ToList();

The currently generated query:

-- Region Parameters
DECLARE @p0 Int = 10
DECLARE @p1 Int = 10
-- EndRegion
SELECT [t2].[OrderId], [t2].[CustomerId]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t1].[OrderId], [t1].[CustomerId]
    FROM (
        SELECT DISTINCT [t0].[OrderId], [t0].[CustomerId]
        FROM [Order] AS [t0]
        ) AS [t1]
    ) AS [t2]
WHERE [t2].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t2].[ROW_NUMBER]

The preferred query:

SELECT *
FROM [Order]
ORDER BY LastModifiedTimestamp
    OFFSET 10000 ROWS
    FETCH NEXT 10000 ROWS ONLY

Do you have any idea why the generated query use ROW_NUMBER() and BETWEEN syntax? What can I do to force Linq to generate OFFSET/FETCH query?

Answer 1

等待大神解答