Dapper: Paging large results

[PeterWone]

What's the recommended approach to fetching a particular page from a large result? Is there something like Linq's Skip and Take? As of MSSQL2012 you can use OFFSET skip FETCH NEXT take ONLY but Linq was able to achieve this with older versions.

[RezaAb]

Hi.
you must create new function and run new query like below.

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNUMBER BETWEEN (({PageNumber}-1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage})

in this query you can use these properties:
{SelectColumns} = list of columns
{TableName}
{PageNumber}
{RowsPerPage}
{OrderBy} = list of columns
{WhereClause} = your conditions
{Offset} = (pageNumber - 1) * rowsPerPage

you can add your new function to list of extensions.

[PeterWone]

Hah yes, between asking that and your answer I used SqlTrace to examine what Linq2Sql actually sends and it is structurally identical to your recommendation. So performance will be identical! Thank you for presenting this as a preferred solution.

[NickCraver]

@PeterWone Are you all set? Just cleaning up issues prepping for 2.0 here.

[PeterWone]

Can I get by? Yes. Would skip/take be awesome if baked into Dapper? Yes they would. Is it a formula transform? Yes. Could I do it myself? Possibly. But I don't have familiarity with Dapper innards, I'm new to it. You can close this as an issue, it has a good answer.

[NickCraver]

@PeterWone Gotcha, thanks!

For others finding this:
We simply don't generate SQL in Dapper, outside of the very few things in contrib. It's not something Dapper could trivially add, it's provider agnostic and this syntax differs by provider at a minimum. Beyond that though, where in the query does it go? Is another question altogether.

I'd say if you're after SQL generation, Dapper may simply not be the ORM for you :) That's just a different direction than the goals Dapper is after: simplicity, efficiency, and performance across many providers.