Dapper: Page<T> helper method

[mdissel]

New helper method to return a Page object that can be used for paging. So that in one call the total count and currentpage is requested from the database.
Sample:

// Results from paged request
public class Page<T> 
{
            public long CurrentPage { get; set; }
            public long TotalPages { get; set; }
            public long TotalItems { get; set; }
            public long ItemsPerPage { get; set; }
            public List<T> Items { get; set; }
}

[NickCraver]

How would you actually implement this, let's say in SQL server? What would the query look like? There's no cheap way to do this, and the workaround ways can be unexpectedly expensive in many common scenarios.

Note: I've asked for this from Microsoft recently: why can't we return a total with a flag as metadata on the connection when using FETCH? But that functionality doesn't exist on most platforms - so I'm not sure how Dapper would possibly do this.

If this is purely for client-side paging after the result set - I'm all for that, but it doesn't really belong in Dapper. Such a method is going to be tailored for the app in many cases and is just outside the scope of a database ORM. For example, at Stack Overflow we use our paged list on various sources: memory cache, APIs, databases, etc.

[mdissel]

It just a helper method executing two queries using the same where statement, one with Count(*) and the other one with the page limits.. You could even run this in one command, returning two resultsets (if supported by the driver).

[NickCraver]

@mdissel I think I understand your use case, but it doesn't really translate well. The encapsulation in Dapper point I made still holds, and moreover the query is likely to change between the count and the full query. For example: the paging is different per provider and can't really be parsed (Dapper doesn't parse any queries, that would be a _huge_ change) and bits of the query can and generally should shift for maximum efficiency. Some examples: we don't need left joins in some cases (where 1:1 is assured), and we don't need to order (most RDBMS would error even) but for the paged set we _have_ to order for consistency (or it'll just result in bad data).

All of those arguments are a bit moot though since any alternatives/solutions involve parsing and changing the query. If that wasn't the case, then a MultiRead is already a good option which can be easily wrapped to a particular team's conventions. Wrapping it outside of Dapper also allows the paging model reuse across the application no matter what the data source (so it also works if caching is in play - which is important for many).

[mdissel]

Here's a sample from NPoco..
https://github.com/schotime/NPoco/blob/8334a03ff06d2bbe8dcd4caf0c342cd4730202c7/src/NPoco/Linq/SimpleQueryProvider.cs#L187

[NickCraver]

@mdissel I understand what you're asking for...I'm saying it doesn't make sense when _you_ generate the query. NPoco generates the SQL, where as you give Dapper the SQL (which can be much more optimized). The libraries have different goals in mind and different performance characteristics. For example, I can't give that query in NPoco OPTION clauses, etc. since it generates the limit statement and such via _sqlExpression = _sqlExpression.Limit(offset, pageSize);. There may be something _like_ this that could live in Contrib with the addition of some helpful SqlBuilder templates others have proposed, but I wouldn't be sure of a place there either.

[programcsharp]

The way we do this for our paged dapper results in http://github.com/programcsharp/griddly is to accept a query and the order by separately, then wrap the given SQL in a CTE to grab out the count, page, and order it all in one go. This only works in SQL 2012+, however.

Something like:

;WITH _data AS (
    {0}
),
    _count AS (
        SELECT COUNT(0) AS OverallCount FROM _data
)
SELECT * FROM _data CROSS APPLY _count ORDER BY {1} OFFSET {2} ROWS FETCH NEXT {3} ROWS ONLY

Where {0} is the passed in SQL, {1} is the ORDER BY, {2} is the page offset and {3} is the page size.

I'm not sure how generally useful this is, but figured it might help.

[programcsharp]

For reference, the SQL 2005-2008 version looks like:

;WITH _data AS (
    {0}
),
    _count AS (
        SELECT COUNT(0) AS OverallCount FROM _data
)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY {1}) AS row_num FROM _data CROSS APPLY _count) x
WHERE row_num BETWEEN {2} AND {3} ORDER BY {1}

[NickCraver]

Closing this out as it's not something we want in Dapper core I believe - reasoning stated earlier in the thread.

[pimbrouwers]

@NickCraver so sorry to bump this old thread. But something here really piqued my interest. Could you elaborate on this:

"...at Stack Overflow we use our paged list on various sources: memory cache, APIs, databases, etc."

Thanks Nick.

[chrsas]

@programcsharp Thanks for your scenario, but CROSS APPLY will be more efficient after paging.