Operations · 3 min read

SkipQuery

This method queries rows from the database by skipping a number of rows and taking the next set.

Code Snippets

The following example skips the first 40 active rows of the [dbo].[Person] table, ordered by creation date, and returns the next 20.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var skip = 40; // The number of rows to skip
    var rowsPerBatch = 20; // The number of rows to return
    var people = connection.SkipQuery<Person>(skip: skip,
        rowsPerBatch: rowsPerBatch,
        orderBy: orderBy,
        where: e => e.IsActive == true);
}

skip is a number of rows, not a page index. Skipping 0 rows returns the first set.

The following example returns the first 20 rows.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery<Person>(skip: 0,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: e => e.IsActive == true);
}

SkipQuery vs BatchQuery

Both operations return a slice of an ordered result set. They differ only in how you position the slice.

Operation	Positioning argument	Example (20 rows per batch)	Rows returned
BatchQuery	`page` — a zero-based batch index.	`page: 2`	Rows 41-60
SkipQuery	`skip` — the number of rows to skip.	`skip: 40`	Rows 41-60

Use SkipQuery when the offset does not fall on a batch boundary (for example, resuming from row 15 or a cursor-style “load more”). Use BatchQuery when you are paging in fixed-size batches.

Targeting a Table

To target a specific table, pass the literal table name.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery<Person>("Person",
        skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: e => e.IsActive == true);
}

Or via dynamics.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery("Person",
        skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: new { IsActive = true });
}

Specific Columns

To query specific columns, pass a list of fields.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var fields = Field.Parse<Person>(e => new
    {
        e.Id,
        e.Name,
        e.DateInsertedUtc
    });
    var people = connection.SkipQuery<Person>(skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: e => e.IsActive == true,
        fields: fields);
}

Or via dynamics.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery("Person",
        skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: new { IsActive = true },
        fields: Field.From("Id", "Name", "DateInsertedUtc"));
}

Filtering the Results

Use QueryGroup or QueryField to build more complex WHERE expressions.

using (var connection = new SqlConnection(connectionString))
{
    var where = new []
    {
        new QueryField("IsActive", true),
        new QueryField("DateInsertedUtc", Operation.GreaterThanOrEqual, DateTime.UtcNow.Date.AddDays(-1))
    };
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery("Person",
        skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: where);
}

Table Hints

Pass a table hint via the hints argument.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery<Person>(skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: e => e.IsActive == true,
        hints: "WITH (NOLOCK)");
}

Or use the SqlServerTableHints class.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new { DateInsertedUtc = Order.Descending });
    var people = connection.SkipQuery<Person>(skip: 40,
        rowsPerBatch: 20,
        orderBy: orderBy,
        where: e => e.IsActive == true,
        hints: SqlServerTableHints.NoLock);
}

Ordering and Validation

Skipping rows is only meaningful against a stable order, so the orderBy argument is required. The statement builder rejects the call when:

orderBy is null or empty.
skip is less than 0.
rowsPerBatch is less than 1.
No fields can be resolved for the target table.

Every field in orderBy must exist on the target table, otherwise the call fails before any SQL is sent. Order by a unique or indexed column (or add a unique tie-breaker such as the primary key) to keep results stable between calls.

How It Works

When you call SkipQuery, the library:

Resolves the fields — your fields argument, mapped to the entity, or all of the table’s columns when none are passed.
Converts the where argument (expression, dynamic, QueryField or QueryGroup) into a single QueryGroup.
Validates the orderBy fields against the table, then asks the statement builder to build the SQL through its CreateSkipQuery method. The generated text is cached, so repeated calls with the same shape do not rebuild it.
Executes the statement through the DbConnection (honoring commandTimeout, transaction and trace), using TraceKeys.SkipQuery as the default trace key.
Materializes the rows into the entity type (or dynamics).

How the slice is expressed in SQL depends on the provider’s statement builder — for example a ROW_NUMBER() CTE on SQL Server, LIMIT/OFFSET on PostgreSQL, SQLite and MySQL, and FIRST m SKIP n on Firebird. See the provider’s statement builder page for details.

SkipQuery also has an Async counterpart, and is exposed through BaseRepository and DbRepository.