CDS OData API $count returns incorrect value

[Anonymous]

I was playing around with CDS' OData API and I noticed that the count of records returned when specifying the $count=true param or when appending $count to the request path maxed out at 5000 regardless of the actual record count. Probably not coincidentally, this also looks like it's the max page size for a response. Quoting the OData v4.0 spec section 11.2.5.5:

The $count system query option ignores any $top, $skip, or $expand query options, and returns the total count of results across all pages including only those results matching any specified $filter and $search. Clients should be aware that the count returned inline may not exactly equal the actual number of items returned, due to latency between calculating the count and enumerating the last value or due to inexact calculations on the service.

The spec allows room for a count to be inexact due to latency or inexact calculations, so maybe this is a grey area. But it feels like taking some pretty extreme liberties to say the actual count is 2000% of the estimate because the count is just inexact. This isn't a huge deal since the pages each provide a next-link if there's overflow, but $count should return a correct(ish) amount or be disabled like $search or $skip.

[ben-thompson]

The logic of returning 5000 records when there are more than 5000 results has been there as long as I remember (CRM 2011 days). Fixing that issue now probably create bigger issues than it solves as there will be a lot of custom code that goes if recordcount==5000

[rajyraman]

@Anonymous- It is by design. The $count is capped at 5000. You should use RetrieveTotalRecordCount if you want the accurate count.

Refer https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api#retrieve-a-count-of-entities

https://crmtipoftheday.com/1375/get-record-count-for-entities/

[ben-thompson · Accepted solution]

@rajyraman RetrieveTotalRecordCount  returns the unfiltered total number of records in that entity. So if the entity has 100,000 records and your filtered results contain 6,000 records it won't return 6,000 it will return 100,000.

I didn't mention it in my post because the lack of filtering support means it probably doesn't solve the issue - RetrieveTotalRecordCount solves 1 simple issue and is definitely one of those issues where MS have created something half baked to tick a feature box issue.

[Anonymous]

Thanks, this is probably as close to a solution as I'll get.

[Anonymous · Accepted solution]

Recently discovered OData aggregation which let me get around the 5000 count limit. Still looks like there's a 50,000 record limit on aggregation queries, but that's good enough for me.

For the curious, the request URL looks something like this:

https://{{my_cool_org}}.crm.dynamics.com/api/data/v9.1/{{my_cool_entity}}?$filter=modifiedon gt 2020-07-31T00:05:00-07:00&$apply=aggregate($count as Count)

And the response:

{
    "@odata.context": "https://{{my_cool_org}}.crm.dynamics.com/api/data/v9.1/$metadata#{{my_cool_entity}}",
    "value": [
        {
            "Count": 49689
        }
    ]
}

[ScottDurow]

It's also worth noting that RetrieveTotalRecordCount will return an *Approximate* record count since the total is cached and not refreshed everytime a new record is inserted/deleted.