[Enhancement]: Add $skip, $take, and $count

[JerryNixon]

What is it?

• Introduce $skip and $take for index-based pagination in REST queries.
• Enable scalar $count queries for total record counts.
• Enhance flexibility in handling large datasets with new query parameters.

This enhancement adds three new query parameters directly to the REST endpoints:

1. $skip: Skips the first N records in the result set.
2. $take: Limits the result set to N records.
3. $count: Returns a scalar integer representing the total number of records matching the filter.

These parameters are critical for UI virtualization and efficient handling of large datasets, offering developers more control over how they interact with data in paginated scenarios.

Example Query Usage

$skip and $take (Index-Based Pagination):

These parameters allow for index-based pagination, a popular method in UIs that need to jump to specific pages or handle infinite scrolls.

GET https://localhost/api/books?$skip=10&$take=20&$filter=pages gt 1000

This query skips the first 10 records and retrieves the next 20 where the number of pages is greater than 1000.

$count (Record Counting):

The $count keyword returns the total number of records that match the applied filter. This is helpful for calculating the total number of pages in a paginated UI.

GET https://localhost/api/books?$select=$count&$filter=pages gt 1000

This returns the total count of books where the number of pages is greater than 1000, making it easier for a UI to display how many pages are available.

Difference from Existing Pagination:

• Cursor-Based Pagination: The existing pagination method focuses on fetching a set number of records at a time and is more suitable for scenarios where continuity is important.
• Index-Based Pagination (with $skip/$take): Ideal for UIs that need to jump to arbitrary pages without tracking a cursor or sequence.

Note: $skip and $take are identical in behavior to .NET LINQ Skip() and Take() methods.

Discussion

• Support for virtualized UI components that need index-based pagination.
• Allows pagination controls to show total pages using $count.
• Provides flexibility for developers to choose between cursor-based and index-based pagination methods based on specific requirements.

Related Issues to Close:

• [Enhancement]: $first & $after keywords in REST & GQL #2246
• [Enhancement]: hasPreviousPage / Before Cursor not found #2238
• [Enhancement]: nextLink Relative Path Response Enhancement #1936
• Optimize how the additional row in paginated result is chopped off #1433