Arm your webapi OData paging query

This paper belongs to OData series

catalog

Introduction

Paging is an unavoidable problem in data request. When there is a lot of data, all data can be returned at one time through GET request, which is not only under performance, but also hard to display.

The principle of paging is that the client requests the server, and the data returned by the server is limited (limited to pageSize). At the same time, it returns a total count of data, which is convenient for the client to process. There is another implementation that uses nextlink to indicate the location of the next page.

Traditional realization

In the traditional implementation, I prefer the Skip and Take methods of LINQ.

/// <summary>
///GET request with parameters
/// </summary>
/// <returns></returns>
[HttpGet("page")]
[ProducesResponseType(typeof(ReturnData<Page<UserInfoModel>>), Status200OK)]
[ProducesResponseType(typeof(ReturnData<string>), Status404NotFound)]
public async Task<ActionResult> Get(string username, int pageNo, int pageSize)
{
    if (pageSize <= 0 || pageNo <= 0) return BadRequest(new ReturnData<string>("Error request"));
    IEnumerable<UserInfoModel> result;
    if (string.IsNullOrWhiteSpace(username))
        result = _userManager.Users.Select(w => ToUserInfoModel(w)).ToList();
    else
        result = _userManager.Users.Select(w => ToUserInfoModel(w)).ToList().Where(w => w.Username.Contains(username));
    var response = result.Skip((pageNo - 1) * pageSize).Take(pageSize);
    Page<UserInfoModel> page = new Page<UserInfoModel>() { PageNo = pageNo, PageSize = pageSize, Result = response, TotalCount = result.Count() };
    return Ok(new ReturnData<Page<UserInfoModel>>(page));
}

Paging function can be realized by passing username, pageNo and pageSize.

OData implementation paging

OData query does not need the back-end to design and accept parameters, implement and other content, and supports two ways to implement paging: client mode and server mode. First, we need to supplement the usage of several keywords: (applicable to OData V4)

$count

The count keyword can be used with the query. Using the form of $count=true, you can append the number of all records that meet the query criteria to the query results.

GET http://localhost:9000/api/devicedatas('ZW000001')?$count=true

Note that this is not a count of the current result returned.

{
    "@odata.context": "http://localhost:9000/api/$metadata#DeviceDatas",
    "@odata.count": 80,
    "value": [
        {
            "id": "554b1ed8-6429-4ad3-83f9-45c7696547e6",
            "deviceId": "ZW000001",
            "timestamp": 1589544960000,
            "dataArray": []
        },
        ...

$skip

The skip keyword specifies the number of records to skip, in the form of $skip=10.

GET http://localhost:9000/api/devicedatas('ZW000001')?$skip=30

The result is that the previous N records are skipped.

$top

The top keyword specifies the first n records that meet the query criteria, using the form of top=10.

GET http://localhost:9000/api/devicedatas('ZW000001')?$top=10

$skiptoken

skiptoken is different from the previous one. skiptoken must return the server. Generally speaking, the server returns the result in the form of the primary key, and the caller calls it directly. Often used in nextlink for server paging.

GET http://localhost:9000/api/devicedatas('ZW000001')?$skiptoken='554b1ed8-6429-4ad3-83f9-45c7696547e6'

Note that this is not a count of the current result returned.

{
    "@odata.context": "http://localhost:9000/api/$metadata#DeviceDatas",
    "value": [
        {
            "id": "554b1ed8-6429-4ad3-83f9-45c7696547e6",
            "deviceId": "ZW000001",
            "timestamp": 1589544960000,
            "dataArray": []
        },
        ...

Client mode

The client mode is the client-side dominated paging implementation. The number of pages to be paged and so on needs to be specified by the client. For the client, it is flexible. It mainly uses count, skip and top keywords.

  1. By default, the server returns all records.
  2. If we paginate 10 records per page, we should use $count = true & $skip = 0 & $top = 10 to get the first page of data with data count for the first request (request the first page).
  3. According to the first request to get the data count, you can quickly calculate the total number of pages. For example, if count=72 is returned, the total number of pages should be 72 / 10 + 1 = 8 (the last page has only 2 data)
  4. The second page should be $count = true & $skip = 10 & $top = 10
GET http://localhost:9000/api/devicedatas('ZW000001')?$count=true&$skip=10&$top=10
  • These commands need to be enabled first. You can startup.cs Modified in:
app.UseMvc(
    routeBuilder =>
    {
        // the following will not work as expected
        // BUG: https://github.com/OData/WebApi/issues/1837
        // routeBuilder.SetDefaultODataOptions( new ODataOptions() { UrlKeyDelimiter = Parentheses } );
        routeBuilder.ServiceProvider.GetRequiredService<ODataOptions>().UrlKeyDelimiter = Parentheses;

        // global odata query options
        //routeBuilder.EnableDependencyInjection();
        routeBuilder.Select().Expand().Filter().OrderBy().MaxTop(600).Count().SkipToken();

        routeBuilder.MapVersionedODataRoutes("odata", "api", modelBuilder.GetEdmModels());
    });

Server mode

The client mode is flexible, but there is a problem that is not easy to deal with: in the process of two requests by the client, the data changes, which will encounter some unexpected problems, such as deleting some of the data, so a piece of data is likely to appear in two pages at the same time. Therefore, we can let the server do paging for us, manage all the data, and sense the data changes of the two requests in time, without this problem.

The server mode needs to use the skiptoken and pagesize settings.

Server mode, client request collection, server returns part of the data, and provides a nextlink. If the client directly requests this link, more data can be obtained.

skiptoken enable can refer to the above client mode code. pagesize is the setting of how many pieces of data the server can return per page at most. It can be specified globally or in specific methods.

[ODataRoute]
[EnableQuery(PageSize = 1)]
[ProducesResponseType(typeof(ODataValue<IEnumerable<DeviceInfo>>), Status200OK)]
public IActionResult Get()
{
    return Ok(_context.DeviceInfoes.AsQueryable());
}

Try to request in the original way.

GET http://localhost:9000/api/DeviceInfoes?$count=true

The returned result is as follows. You can see that there is one more data at the end of the returned data@ odata.nextLink , this direct click can directly request the next set of data. In the next set of data, there will be the address of the next set of data until the last set of data.

{
    "@odata.context": "http://localhost:9000/api/$metadata#DeviceInfoes",
    "@odata.count": 3,
    "value": [
        {
            "deviceId": "ZW000001",
            "name": null,
            "deviceType": null,
            "imagePath": null,
            "layout": []
        }
    ],
    "@odata.nextLink": "http://localhost:9000/api/DeviceInfoes?$count=true&$skiptoken=deviceId-'ZW000001'"
}

be careful:

  • My primary key here uses string type and EF core Three , the direct request will return the server error. You need to specify the string comparison mode by yourself. You can use AsEnumerable() to System.Linq Processing. If the primary key used is numeric, there should be no such problem. Refer here
  • We can apply the syntax of client mode such as skip to construct the data we need.

After reading the server mode, I feel that this mode is a little stiff. I can only get the next link one by one. What should I do when I want to jump several pages directly?

First you need to understand the paging pattern, we request http://services.odata.org/V4/TripPinService/People The nextlink returned will look like this:

"@odata.nextLink": "https://services.odata.org/V4/TripPinService/People?%24skiptoken=8"

I used an official address here, and returned 8 pieces of data, indicating the location of the next link. Obviously, this skiptoken=8 starts from the 9th one, so the specified address is only the first one. We can modify it to other numbers. (as mentioned earlier, skiptoken must be generated by service, which means that the query mode later needs to be generated by the server.)

So for the third page, it's skiptoken=16. However, because the server specifies page size 8, our query is still inconvenient. You can implement this by inheriting EnableQueryAttribute, replacing the just [EnableQueryAttribute] with [MyEnableQueryAttribute]. carry

public class MyEnableQueryAttribute : EnableQueryAttribute
{
    public override IQueryable ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
    {
        int pagesize = xxx;
        var result = queryOptions.ApplyTo(queryable, new ODataQuerySettings { PageSize = pagesize }); 
        return result;
    }
} 

summary

OData can easily implement paging query by using client mode paging and server-side paging. A GET query is all done, Soha! Don't ask is shuttle!

reference material

Tags: github

Posted on Tue, 19 May 2020 07:57:09 -0400 by Jyotsna