D365 Custom JSON Web Service with Pagination

I got a request for a RESTful API with Pagination; here is my proof of concept:

There are a few keys to this:

• A queryRun object must be used (vs SELECT statement) in order to use enablePositionPaging
• The query MUST have a sort on it

Without using queryRun, you would have to do some hacky stuff with x++ SQL that would not be performant.  And that still may not work.

Without a sort, you will get the error "An exception occured when invoking the operation - Paging is not supported for queries that do not have an ordering property."

class testPagingCustomers
{
    public str getCustomers(int64 _startPosition, int64 _pageSize, boolean _includeTotalCount)
    {
        str json;
        Query query;
        QueryRun queryRun;

        query = new Query();
        QueryBuildDataSource qbd = query.addDataSource(tableNum(CustTable));
        qbd.addSortField(fieldNum(CustTable, AccountNum), SortOrder::Ascending);

        // Set up paging
        queryRun = new QueryRun(query);
        queryRun.enablePositionPaging(true);
        queryRun.addPageRange(_startPosition, _pageSize);
        
        List customers = new List(Types::Class);

        while (queryRun.next())
        {
            CustTable custTable = queryRun.get(tableNum(CustTable));
            testCustomersLinesContract testCustomersLinesContract = new testCustomersLinesContract();

            testCustomersLinesContract.parmAccountNum(CustTable.AccountNum);
            testCustomersLinesContract.parmCustGroup(custTable.CustGroup);
            testCustomersLinesContract.parmCustName(custTable.name());

            customers.addEnd(testCustomersLinesContract);
        }

        testCustomersContract testCustomersContract = new testCustomersContract();
        testCustomersContract.parmCustomers(customers);

        if (_includeTotalCount)
        {
            testCustomersContract.parmTotalCount(SysQuery::countLoops(queryRun));
        }

        json =  FormJsonSerializer::serializeClass(testCustomersContract);

        return json;

    }

}

To test or implement the class, assign it to a service and service group in D365.  Once deployed, you should be able to navigate in a browser to verify:

Then use the verified URI to make a POST request: