Paginated Queries - adamfoneil/Dapper.QX GitHub Wiki

A common requirement you'll have is returning a subset of records from a larger query using some kind of page parameter. This is easy to do with Dapper.QX, and there are a couple ways to approach. Here's a very simple example. I've left out the result type definition to keep it short.

In a nutshell, you use the {offset} token within your SQL statement to indicate where the OFFSET/FETCH clause is injected. This always comes after the ORDER BY clause. Then you add a nullable int property decorated with the [Offset] attribute that represents a page number parameter. The [Offset] attribute requires a number that indicates the number of records per page to return. In this example, I'm using the value 30 to indicate that a page will have 30 rows.

public class MyQuery : Query<MyResultType>
{
    public MyQuery() : base("SELECT * FROM [dbo].[MyHugeTable] ORDER BY [SomeColumn] {offset}")

    [Offset(30)]
    public int? Page { get; set; }
}

Execute the query like this. This assumes a fictional GetConnection method that opens a database connection. Page number values are 0-based, so assign 0 to the Page property to get the first page of results:

var results = await new MyQuery() { Page = 0 }.ExecuteAsync(GetConnection);

This will resolve this SQL:

SELECT * FROM [dbo].[HugeTable] ORDER BY [SomeColumn] OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY

As with any Dapper.QX query, you can open a connection explicitly and execute a query like this, passing the connection as an argument to the ExecuteAsync method. This is preferable if you have multiple queries to run against the same connection. Remember also you can combine this paging technique with all other query property features to give you a flexible, type-safe, performant query approach.

using (var cn = GetConnection())
{
    var results = await new MyQuery() { Page = 0 }.ExecuteAsync(cn);
}

If you omit the Page property, then all results are returned.

As an attribute, [Offset] requires a constant expression for the page size. So, you can't use a variable or method call to set the page size. If you need to customize the page size, you can pass an optional newPageSize argument like this. In this example, the page size is changed from 30 to 50.

var results = await new MyQuery() { Page = 5 }.ExecuteAsync(GetConnection, newPageSize: 50);

Using PageableQuery<T>

If you need the total number of pages in a paginated query, use PageableQuery<T> instead of Query<T>, like this. In this case, you omit the Page property because it's already part of the base class, with a default page size of 30.

public class MyQuery : PageableQuery<MyResultType>
{
    public MyQuery() : base("SELECT * FROM [dbo].[MyHugeTable] ORDER BY [SomeColumn] {offset}")
}

Get the total page count with the GetPageCountAsync method like this:

using (var cn = GetConnection())
{
    var query = new MyQuery() { Page = 3 };
    var results = await query.ExecuteAsync(cn);
    var totalPages = await query.GetPageCountAsync(cn);
}

The GetPageCountAsync method has a newPageSize argument so you can override the default 30-row page size with your own desired page size, like this:

using (var cn = GetConnection())
{
    var query = new MyQuery() { Page = 3 };
    var results = await query.ExecuteAsync(cn);
    var totalPages = await query.GetPageCountAsync(cn, newPageSize: 50);
}

You can also get the total pages and row count of a query with GetMetricsAsync, which returns a ResultMetrics object.