Dapper. Paging

dapper paging

Question

I am trying Dapper ORM and I am querying a a Posts table.

But I would like to get paged results ...

1 - How can I do this? Isn't there a helper for this?

2 - Can Dapper Query return an IQueryable?

Thank You, Miguel

Accepted Answer

1) Dapper doesn't have a built-in pagination feature. But its not too hard to implement it directly in the query. Example:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY InsertDate) AS RowNum, *
          FROM      Posts
          WHERE     InsertDate >= '1900-01-01'
        ) AS result
WHERE   RowNum >= 1 // *your pagination parameters
    AND RowNum < 20  //*
ORDER BY RowNum

Requires SQL Server 2005+

2) Dapper returns an IEnumerable<T>.


Popular Answer

You didn't specify a database or version. If you're lucky enough to be able to use the brand new SQL Server 2012 and have access to MSDN, you can use the shiny new OFFSET and FETCH keywords. The following query will skip 20 records and return the next 5.

SELECT * FROM [Posts]
ORDER BY [InsertDate]
OFFSET 20 ROWS
FETCH NEXT 5 ROWS ONLY

Check out http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx#Offset for more info.

Also, it's easy enough to copy the way Massive does it and write your own extension method for IDbConnection. Here's Massive's code.

var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS Row, {0} FROM {3} {4}) AS Paged ", columns, pageSize, orderBy, TableName, where);



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why