If you want to page query results in dynamics CRM then the recommended way is paging cookies:

http://msdn.microsoft.com/en-gb/library/cc151070.aspx

Sounds straightforward, we get a paging cookie back on each resultset, and we simply add this to our request for the next page.

What doesn’t seem well documented is that these paging cookies do not work for a lot of queries, even though they are returned on the first page.

Let’s set up a simple example to demonstrate this.

First we will create a new entity, ‘new_ParentRecord’ with a One-Many relationship to ‘new_ChildRecord’:

image

And create 10 ParentRecords, each linked to 4 child records:

const int NOPARENTS = 10; const int CHILDRENPERPARENT = 4; for (int parentNo = 1; parentNo <= NOPARENTS; parentNo++) { Entity parent = new Entity(new_parentrecord); parent.Attributes[new_name] = String.Format(Parent {0}, parentNo); var parentId = service.Create(parent); for (int childNo = 1; childNo <= CHILDRENPERPARENT; childNo++) { Entity childA = new Entity(new_childrecord); childA.Attributes[new_name] = String.Format(Parent {0} Child A{1}, parentNo, childNo); childA.Attributes[new_parentaid] = new EntityReference(new_parentrecord, parentId); service.Create(childA); } }

Now let’s get a list of all Parent records and their children, using a page size of 5:

<fetch mapping=”logical” count=”5″ version=”1.0″> <entity name=”new_parentrecord”> <attribute name=”new_name” /> <link-entity name=”new_childrecord” from=”new_parentaid” to=”new_parentrecordid”> <attribute name=”new_childrecordid” /> <attribute name=”new_name” /> </link-entity> </entity> </fetch>

This gets converted to the following SQL:

select top 6 “new_parentrecord0”.new_name as “new_name” , “new_parentrecord0”.new_parentrecordId as “new_parentrecordid” , “new_childrecord1”.new_childrecordId as “new_childrecord1.new_childrecordid” , “new_childrecord1”.new_name as “new_childrecord1.new_name” from new_parentrecord as “new_parentrecord0” join new_childrecord as “new_childrecord1” on (“new_parentrecord0”.new_parentrecordId = “new_childrecord1”.new_ParentAId) order by “new_parentrecord0”.new_parentrecordId asc

A couple of points to note, firstly, CRM always retrieves one more record than the page size so it can set the morerecords flag on the results. Secondly, the default sort order is by the primary entity id.

The results returned are:

Parent Name Parent ID Child Name
Parent 1 {F8DAB1AA-3A0F-E411-8189-005056B20097} Parent 1 Child A1
Parent 1 {F8DAB1AA-3A0F-E411-8189-005056B20097} Parent 1 Child A2
Parent 1 {F8DAB1AA-3A0F-E411-8189-005056B20097} Parent 1 Child A3
Parent 1 {F8DAB1AA-3A0F-E411-8189-005056B20097} Parent 1 Child A4
Parent 2 {01DBB1AA-3A0F-E411-8189-005056B20097} Parent 2 Child A1

 

And we get the following paging cookie back:

<cookie page=”1″> <new_parentrecordid last=”{01DBB1AA-3A0F-E411-8189-005056B20097}” first=”{F8DAB1AA-3A0F-E411-8189-005056B20097}” /> </cookie>

The cookie contains the first and last values in the result set for the primary entity sort columns.

We insert this cookie as a hint when we retrieve page 2:

<fetch mapping=”logical” count=”5″ page=”2″ paging-cookie=”&lt;cookie page=&quot;1&quot;&gt;&lt;new_parentrecordid last=&quot;{F8DAB1AA-3A0F-E411-8189-005056B20097}&quot; first=&quot;{F8DAB1AA-3A0F-E411-8189-005056B20097}&quot; /&gt;&lt;/cookie&gt;” version=”1.0″> <entity name=”new_parentrecord”> <attribute name=”new_name” /> <link-entity name=”new_childrecord” from=”new_parentaid” to=”new_parentrecordid”> <attribute name=”new_childrecordid” /> <attribute name=”new_name” /> </link-entity> </entity> </fetch>

Which generates the following SQL:

select top 6 “new_parentrecord0”.new_name as “new_name” , “new_parentrecord0”.new_parentrecordId as “new_parentrecordid” , “new_childrecord1”.new_childrecordId as “new_childrecord1.new_childrecordid” , “new_childrecord1”.new_name as “new_childrecord1.new_name” from new_parentrecord as “new_parentrecord0” join new_childrecord as “new_childrecord1” on (“new_parentrecord0”.new_parentrecordId = “new_childrecord1”.new_ParentAId) where (((“new_parentrecord0”.new_parentrecordId > 01DBB1AA-3A0F-E411-8189-005056B20097))) order by “new_parentrecord0”.new_parentrecordId asc

We can see that CRM is simply using our paging cookie to specify the start point of the search.

Finally the results, page 2:

Parent Name Parent ID Child Name
Parent 3 {0ADBB1AA-3A0F-E411-8189-005056B20097} Parent 3 Child A1
Parent 3 {0ADBB1AA-3A0F-E411-8189-005056B20097} Parent 3 Child A2
Parent 3 {0ADBB1AA-3A0F-E411-8189-005056B20097} Parent 3 Child A3
Parent 3 {0ADBB1AA-3A0F-E411-8189-005056B20097} Parent 3 Child A4
Parent 4 {13DBB1AA-3A0F-E411-8189-005056B20097} Parent 4 Child A1

Uh oh! This isn’t what we wanted at all. We have skipped 3 of the Parent 2 records. In fact, depending on our exact data, we could have skipped any number of records.

This has happened because the last id stored in the paging cookie does not uniquely identify a row, it is shared by 4 different rows.

If the primary entity ids in your resultset are not unique, then paging cookies cannot be used reliably.

 

Personally, it seems like a bug to me that CRM returns a paging cookie that cannot be relied on, in other situations (like when a related entity has a sort column) the paging cookie is not returned.

What can we do? There are a few options to workaround this limitation:

Rewrite your fetchxml so the primary entity Ids are unique.

If we flip the above example around and make the child record the primary entity, then we don’t hit this problem. However, this is impossible to do for queries with multiple one-many relationships from a single entity, or for many-many relationships

Don’t use the paging cookie

You can just specify the page and this will work correctly. However, this is very inefficient if you have many pages, as CRM must retrieve all results for preceding pages. [UPDATE – CRM paging seems quite buggy and even in this case, with non-unique primary Ids it relies on SQL having a stable sort order which generally it does, however if MAXDOP is set to 1 as is recommended, then it will have a stable sort order]

Split your fetchxml into multiple queries that can be paged and join the results in-memory

This is actual preferable sometimes anyway to reduce the network traffic associated with queries with many joins.

Construct your own paging cookie

In the above example, if we pass in the id of the last Parent 1 record in our paging cookie, and effectively treat our first page as only having returned 4 records, then page 2 will contain all the Parent 2 records. However, for this to work you must be able to fit all the rows for a single primary entity on a single page. Also, if your results have a sort order, then the first and last values for all primary entity fields in the sort order must be added to the cookie.

Get our latest articles in your inbox

Enjoyed this article? Sign up for our email newsletter and get real-world information on all things Microsoft, cloud and tech. Your information will be shared with MailChimp but no one else, and you can unsubscribe with one click at any time