By default you’ll get up to 5,000 records that match your query. The top-level <fetch> element supports a few options to help you control this:
top will limit the number of records further, e.g.
<fetch top="10">
<entity name="account">
<all-attributes />
</entity>
</fetch>
When you use top to limit the number of records, you don’t have the option to move on to subsequent pages. You can’t use this to increase the number of records beyond 5,000 – you can only use a number between 1 and 5,000.
In contrast, count and page allow you to move through multiple pages of results and control the size of each page:
<fetch count="100" page="3">
<entity name="account">
<all-attributes />
<order attribute="createdon" />
</entity>
</fetch>
This example will get records 201 – 300. If you are working you way through sequential pages you should also set the paging-cookie attribute with the value of the paging cookie from the previous page.
So when should you use top and when should you use count and page?
If you are only ever interested in the first n records (e.g. you just want the first record that matches your query), use top as you have no need to retrieve multiple result pages.
On the other hand, if you do need to retrieve all the results (or at least, more than “just” the first 5,000), use page to indicate which number page you want. In addition you can optionally use count to control the size of each page – you might set this after some trial-and-error testing of the performance of your query.
SQL Equivalents
In SQL terms, top is equivalent to the SELECT TOP (n) clause. count and page are similar to the OFFSET ... FETCH ... clause.
I’m getting odd behavior from “top,” when there are child records and the sort is on those child records. We had a requirement that wanted us to pull the “oldest” record out of queue — but not by the queue entry date, but based on a date in the target record. We created a fetch query that joined from queue to queueitem to the target entity. We then sort by fields on the target entity, and the “top” clause is just ignored, we get all the records in the queue. “Count” works as expected for this purpose, but it seems like it’s a bug.
Yes, I’ve seen the same. I’ve reported it as a bug but might help if you do the same as well, my ticket number if you want to reference it is 2103230050001004
Hi,
Whenever I try use count and page with fetch xml in Data factory I get this error
“Message”: “Failure happened on ‘Source’ side. ErrorCode=DynamicsOperationFailed,’Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Dynamics operation failed with error code: DynamicsOperationFailed, error message: The Fetch Xml query specified is invalid..,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,”Type=System.Xml.XmlException,Message=Should not specify paging related arributes in your Fetch Xml query.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,'”,
Can you help me
This sounds like a restriction imposed by the Data Factory connector specifically. I believe Data Factory expects to read all the results of a FetchXML query rather than a single specific page – if you have a need to read a single page I suggest you raise this on the Azure Data Factory Community Hub.