cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

CDS OData API $count returns incorrect value

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

@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.

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

View solution in original post

Anonymous
Not applicable

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
        }
    ]
}

 

View solution in original post

6 REPLIES 6
ben-thompson
Solution Sage
Solution Sage

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

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".
rajyraman
MVP

@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/

@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.

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

View solution in original post

Anonymous
Not applicable

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

Anonymous
Not applicable

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
        }
    ]
}

 

View solution in original post

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,300)