Dapper ORM分页和排序

Question

我正在尝试使用Dapper ORM。 我可以使用以下代码从表中查询数据：

Dim comments As List(Of Comment)
Using conn = New SqlConnection(ConnectionString)
    conn.Open()
    comments = conn.Query(Of Comment)("SELECT * from comments where userid = @commentid", New With {.userid= 1})
End Using

Return View(comments)

我想学习如何使用Dapper进行分页/排序。EF有“skip”和“take”可以帮助完成此操作。我知道微型ORM没有内置此功能，但想知道完成此操作的最佳方法。

- ericdc

Answer 1

如果你想在Dapper中使用跳过和获取操作，可以使用T-SQL实现。

SELECT *
FROM
(
SELECT tbl.*, ROW_NUMBER() OVER (ORDER BY ID) rownum
FROM comments as tbl
) seq
 WHERE seq.rownum BETWEEN @x AND @y
 AND userid = @commentid
 ORDER BY seq.rownum

- mattmc3

---

是的，请记住这取决于数据库，Oracle和MySQL有限制和偏移量，Denali有偏移量等。 - Sam Saffron

“where” 应该在内部查询中，对吧？而且最后的查询应该明确指定 “order by rownum”（抄送 @Sam）。 - Marc Gravell

@Marc 是的，你需要在最后加上一个 order by。内部查询中可能需要也可能不需要 where 子句，有时可以在内部查询中添加 TOP @y 来提高性能。 - Sam Saffron

你不应该使用TOP (@y)，而是使用TOP (@pagesize)。因为"@y"代表的是绝对行号，而不是你想要的记录数。 - NickG

Answer 2

你现在可以做到这一点。
你只需要编写一个扩展方法，它接受查询、页面大小和页码，然后你需要追加

。

OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

将查询语句添加到行并执行。
请注意，查询绝对需要一个ORDER-BY子句（至少在T-SQL中）。
这适用于MS-SQL（2012+），PostgreSQL（8.4+）和Oracle（12c+）。
对于MySQL，您必须附加LIMIT offset、page_size。

LIMIT @PageSize * (@PageNumber - 1), @PageSize 

对于Firebird，您需要附加ROWS x TO y。

ROWS (@PageSize * (@PageNumber - 1)) TO (@PageSize * @PageNumber -1) 

对于基于1的索引，起始偏移量为startoffset_base1，结束偏移量为endoffset_base1

StartAt @PageSize * (pagenum - 1) + 1 EndAt @PageSize * (pagenum - 1) + @PageSize

例子：

DECLARE @PageSize int 
DECLARE @PageNumber int 
SET @PageSize = 5
SET @PageNumber = 2

SELECT * FROM T_Users
ORDER BY USR_ID 
-- Must contain "ORDER BY" 
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

关于不同关系数据库管理系统的语法，请参见
http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/

验证：

DECLARE @PageSize int 
SET @PageSize = 5


;WITH CTE AS 
(
    SELECT 1 as pagenum 
    UNION ALL

    SELECT pagenum+1 AS pagenum 
    FROM CTE 
    WHERE CTE.pagenum < 100
)
SELECT 
     pagenum
    ,@PageSize * (pagenum - 1) AS StartOFFSETBase0
    --,@PageSize * (pagenum - 1) + @PageSize - 1 AS EndOFFSETBase0
    ,@PageSize * pagenum - 1 AS EndOFFSETBase0 -- Simplified

    ,@PageSize * (pagenum - 1) + 1 AS StartOFFSETBase1
    ,@PageSize * (pagenum - 1) + @PageSize AS EndOFFSETBase1
FROM CTE 

- Stefan Steiger