实现分页的有效方法

LinqToSql 将自动转换。跳过(N1)。为您使用(N2) TSQL 语法。事实上，你在 Linq 做的每一个“查询”，实际上只是在后台为你创建一个 SQL 查询。要对此进行测试，只需在应用程序运行时运行 SQLProfiler。

跳过/采取的方法对我和其他人来说都非常有效。

Out of curiosity, what type of self-paging query do you have, that you believe is more efficient than Linq's skip/take?

我们在存储过程中使用一个包装在 Dynamic SQL 中的 CTE (因为我们的应用程序需要对数据服务器端进行动态排序)。如果你愿意，我可以提供一个基本的例子。

我还没有机会看到 LINQ 生成的 T/SQL。有人能发一个样本吗？

我们不使用 LINQ 或直接访问表，因为我们需要额外的安全层(授予动态 SQL 在某种程度上破坏了这一点)。

像这样的东西应该可以解决这个问题。您可以为参数添加参数化的值，等等。

exec sp_executesql 'WITH MyCTE AS (
SELECT TOP (10) ROW_NUMBER () OVER ' + @SortingColumn + ' as RowID, Col1, Col2
FROM MyTable
WHERE Col4 = ''Something''
)
SELECT *
FROM MyCTE
WHERE RowID BETWEEN 10 and 20'

如果你在 linq 上执行 skip(n).take(m)方法(使用 SQL 2005/2008作为数据库服务器) ，试图给你一个简短的答案，你的查询将使用 Select ROW_NUMBER() Over ...语句，以某种方式在 SQL 引擎中直接分页。

举个例子，我有一个名为 mtcity的 db 表，我编写了以下查询(同样可以使用 linq 到实体) :

using (DataClasses1DataContext c = new DataClasses1DataContext())
{
var query = (from MtCity2 c1 in c.MtCity2s
select c1).Skip(3).Take(3);
//Doing something with the query.
}

结果查询将是:

SELECT [t1].[CodCity],
[t1].[CodCountry],
[t1].[CodRegion],
[t1].[Name],
[t1].[Code]
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY [t0].[CodCity],
[t0].[CodCountry],
[t0].[CodRegion],
[t0].[Name],
[t0].[Code]) AS [ROW_NUMBER],
[t0].[CodCity],
[t0].[CodCountry],
[t0].[CodRegion],
[t0].[Name],
[t0].[Code]
FROM [dbo].[MtCity] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

这是一个窗口数据访问(非常酷，因为从一开始就会返回数据，并且只要满足条件就会访问表)。这将非常类似于:

With CityEntities As
(
Select ROW_NUMBER() Over (Order By CodCity) As Row,
CodCity //here is only accessed by the Index as CodCity is the primary
From dbo.mtcity
)
Select [t0].[CodCity],
[t0].[CodCountry],
[t0].[CodRegion],
[t0].[Name],
[t0].[Code]
From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

除了这个例外，第二个查询的执行速度会快于 linq 结果，因为它只使用索引来创建数据访问窗口; 这意味着，如果你需要一些过滤，过滤应该(或者必须)在实体列表中(行被创建的地方) ，一些索引也应该被创建以保持良好的性能。

现在，什么更好？

If you have pretty much solid workflow in your logic, implementing the proper SQL way will be complicated. In that case LINQ will be the solution.

如果您能够将这部分逻辑直接降低到 SQL (在存储过程中) ，那就更好了，因为您可以实现我向您展示的第二个查询(使用索引) ，并允许 SQL 生成和存储查询的执行计划(提高性能)。

你可以进一步提高性能，检查这一点

From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

如果你以这种方式使用 from，它会给出更好的结果:

From   dbo.MtCity  t0
Inner Join  CityEntities c on c.CodCity = t0.CodCity

原因: 因为您正在使用 CityEntities 表中的 where 类，它将在加入 MtCity 之前消除许多记录，所以100% 确定它将提高性能许多倍..。

不管怎样，罗德里格尔普的回答很有帮助。

谢谢

尝试使用

FROM [TableX]
ORDER BY [FieldX]
OFFSET 500 ROWS
FETCH NEXT 100 ROWS ONLY

在 SQL 服务器中获取从501到600的行，而不需要在内存中加载它们。注意，这种语法只能在 SQL Server 2012中使用

While LINQ-to-SQL will generate an OFFSET clause (possibly emulated using ROW_NUMBER() OVER() as others have mentioned), there is an entirely different, much faster way to perform paging in SQL. This is often called the "seek method" as described in 这篇博文.

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

@previousScore和 @previousPlayerId值分别是上一页中最后一条记录的值。这允许您获取“下一页”。如果 ORDER BY的方向是 ASC，只需使用 >代替。

使用上述方法，如果没有首先获取之前的40条记录，就不能立即跳转到第4页。但通常情况下，你不会想要跳那么远。相反，您可以获得更快的查询，根据您的索引，它可能能够在常量时间内获取数据。另外，无论底层数据是否发生变化(例如，在第1页，而您在第4页) ，您的页面都保持“稳定”。

例如，这是在 Web 应用程序中延迟加载更多数据时实现分页的最佳方法。

注意，“搜索方法”也称为 密钥集分页。

在 SQLServer2008中:

DECLARE @PAGE INTEGER = 2
DECLARE @TAKE INTEGER = 50


SELECT [t1].*
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[COLUMNORDER] DESC) AS [ROW_NUMBER], [t0].*
FROM [dbo].[TABLA] AS [t0]
WHERE ([t0].[COLUMNS_CONDITIONS] = 1)
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN ((@PAGE*@TAKE) - (@TAKE-1)) AND (@PAGE*@TAKE)
ORDER BY [t1].[ROW_NUMBER]

在所有的记录 In t1 are only those corresponding to that page

可以通过传递 PageIndex 以这种简单的方式实现分页

Declare @PageIndex INT = 1
Declare  @PageSize INT = 20


Select ROW_NUMBER() OVER ( ORDER BY Products.Name ASC )  AS RowNumber,
Products.ID,
Products.Name
into #Result
From Products


SELECT @RecordCount = COUNT(*) FROM #Results


SELECT *
FROM #Results
WHERE RowNumber
BETWEEN
(@PageIndex -1) * @PageSize + 1
AND
(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

在2008年我们不能使用 Skip ()

方法是:

var MinPageRank = (PageNumber - 1) * NumInPage + 1
var MaxPageRank = PageNumber * NumInPage


var visit = Visita.FromSql($"SELECT * FROM (SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Hora DESC),* FROM Visita WHERE ) A WHERE A.[RANK] BETWEEN {MinPageRank} AND {MaxPageRank}").ToList();

我给出的方法是 SQL 服务器能够实现的最快分页。我在五百万张唱片上测试过。这种方法比 SQLServer 提供的“ OffSET10ROWSFETCHNEXT10ROWSONLY”要好得多。

-- The below given code computes the page numbers and the max row of previous page
-- Replace <<>> with the correct table data.
-- Eg. <<IdentityColumn of Table>> can be EmployeeId and <<Table>> will be dbo.Employees


DECLARE @PageNumber int=1; --1st/2nd/nth page. In stored proc take this as input param.
DECLARE @NoOfRecordsPerPage int=1000;


DECLARE @PageDetails TABLE
(
<<IdentityColumn of Table>> int,
rownum int,
[PageNumber] int
)
INSERT INTO @PageDetails values(0, 0, 0)
;WITH CTE AS
(
SELECT <<IdentityColumn of Table>>, ROW_NUMBER() OVER(ORDER BY <<IdentityColumn of Table>>) rownum FROM <<Table>>
)
Insert into @PageDetails
SELECT <<IdentityColumn of Table>>, CTE.rownum, ROW_NUMBER() OVER (ORDER BY rownum) as [PageNumber] FROM CTE WHERE CTE.rownum%@NoOfRecordsPerPage=0




--SELECT * FROM @PageDetails


-- Actual pagination
SELECT TOP (@NoOfRecordsPerPage)
FROM <<Table>> AS <<Table>>
WHERE <<IdentityColumn of Table>> > (SELECT <<IdentityColumn of Table>> FROM
@PageDetails WHERE PageNumber=@PageNumber)
ORDER BY <<Identity Column of Table>>