Dapper ORM paging and sorting

Question

I am giving the Dapper ORM a try. I am able to query data from a table using the code below:

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)

I am interested to learn how to do paging/sorting using Dapper. EF has "skip" and "take" to help with this. I understand that a micro ORM does not have this built in but would like to know the best way to accomplish this.

score 19 · Accepted Answer · edited May 23 '17 at 12:06

19

If you want to do skip and take with Dapper, you do it with 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

edited May 23 '17 at 12:06

Community

1
1

answered May 07 '11 at 01:50

mattmc3

17,595
7
83
103

2

yes, also keep in mind this is db dependent, oracle and mysql have limit and offset, denali has offset and so on. – Sam Saffron May 07 '11 at 11:08
1

The **where** should be inside the inner query, no? And you should probably have an explicit **order by rownum** on the final query (cc @Sam) – Marc Gravell May 07 '11 at 11:28
1

@Marc yes you would need an order by at then end .. the where may or may not be needed in the inner query, sometimes you can add a TOP @y to the inner query to get a perf boost – Sam Saffron May 07 '11 at 11:30
2

Wouldn't you do TOP (@pagesize) rather than TOP (@y)? "@y" will be an absolute rownumber not the number of records you want? – NickG Nov 14 '12 at 11:53

Stefan Steiger · Answer 2 · 2016-10-12T10:18:41.010

You could do it now.
All you'd need to do is write an extension method that takes Query and PageSize and PageNumber, then you need to append the

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

line to the query and execute.
Note that the query absolutely requires an ORDER-BY clause (at least in T-SQL).
This would work for MS-SQL (2012+), PostgreSQL (8.4+), and Oracle (12c+).
For MySQL, you'd have to append LIMIT offset, page_size.

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

For Firebird, you'd have to append ROWS x TO y

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

For a base-1 index, it would be from startoffset_base1 to endoffset_base1

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

Example:

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;

For the syntax on various different RDBMS, see
http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/

Verification:

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

Dapper ORM paging and sorting

2 Answers2