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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (914)