Collection Counts

Collection Counts

OData services have an optional feature that allows operations that return a collection of entities to instead only return the number of entities in a particular result set. This is conceptually similar to the SQL SELECT COUNT operation.

Remember though that underlying data is still held within ISAM or relative files; it's still necessary to run the full query in order to determine the result count, but at least all of that work happens in the back end environment and does not propagate all the way through the web service. Because of this, it can still be a useful feature.

To obtain the count of a result set a consumer appends /$count to the end of a query URL, but in order to be supported, you must first enable the capability in your REST API. That is done by editing the code generation batch file (regen.bat) and removing the comment from the ENABLE_COUNT line.

Having done so, save the changes to the batch file and execute it to regenerate your code.

Enabling Collection Counts

To add support for collection counts you must enable the set ENABLE_COUNT option in the regen batch file by removing the rem comments from the beginning of the line, like this:

set ENABLE_COUNT=-define ENABLE_COUNT

Generating the Code

Check that you have saved the changes to the batch file, then execute it to regenerate your code.

What Changed

Enabling collection counts does not cause additional files to be generated, but causes additional content to be generated into various files, specifically:

• A new statement builder.Count() is added to the OData configuration section of the Startup class in the Services project.
• The additional capability is documented in the API Documentation if enabled.
• Additional tests are added to the postman file if enabled.

Testing the New Functionality

In Visual Studio, press F5 (start debugging) to start the self-hosting application. Once again you should see the console window appear, and messages confirming that your service is running, open your web browser and navigate to the "all customers" endpoint:

https://localhost:8086/odata/v1/Customers

You should see a JSON response that includes the full record for all 38 customers in the sample customer data file. Now add /$count to the end of the URL and re-execute the query:

https://localhost:8086/odata/v1/Customers/$count

The response should now be simply:

38

Another way to use this feature is to use a slightly different syntax to instruct OData to include the result count in with the result data. For example, the following URL:

https://localhost:8086/odata/v1/Customers?$count=true

This request causes all customer entities to be returned, but you will notice that an additional piece of metadata @odata.count is included in the response:

{
    "@odata.context": "https://harmonycoreapp.azurewebsites.net/odata/v1/$metadata#Customers",
    "@odata.count": 38,
    "value": [
      {
        "@odata.etag": "W/\"YmluYXJ5J0FDQUFBQUFBNHhsU3FRPT0n\"",
        "CustomerNumber": 1,

Note that this functionality can be combined with other OData query expressions, for example:

https://localhost:8086/odata/v1/Customers?$filter=State+eq+'CA'&$count=true

Returns a subset of the collection of customers, based on the specified filter criteria, and also includes the @odata.count metadata.

{
  "@odata.context": "https://harmonycoreapp.azurewebsites.net/odata/v1/$metadata#Customers",
  "@odata.count": 13,
  "value": [
    {
      "@odata.etag": "W/\"YmluYXJ5J0FDQUFBQUFBNHhsU3FRPT0n\"",
      "CustomerNumber": 1,

NOTE: The $count functionality should work with all endpoints and queries that return a collection, but currently it is only working with the full collection endpoint. We think this is because of a limitation in the underlying OData library that we use, and we'll be investigating further.