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

Answer 1

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); 

Answer 2

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>.