BizTalk Server – Dynamics CRM Online / On Premise CRM Paging Cookie

Published on : Oct 23, 2016

Rachit Sikroria

Author

Problem Scenario

While communicating with CRM Entities using Retrieve Multiple operation using request response pattern there are some scenarios where you need to process bulk request queries like using pattern matching Contains Data, Not null, etc.

These query results bulk data to BizTalk as a response in XML Format with all the details.

Scenario 1: Assume you need to process 10,000 records by interacting with CRM.
OR
Scenario 2: Single fetch Query, Expression can get you up to 5,000 records.

Handling these huge file in BizTalk at once could result in performance impact. Now think of scenario when you have to process multiples request of such huge records. 5000 records XML data can be up to 500 MB plus, depending on the data you retrieve like column / attributes.

Solution

In such scenario using the below pattern will help you to process the limited records with multiple calls without overloading BizTalk with huge data.

There are multiple ways with which you can query CRM

Retrieve Operation
Retrieve Multiple Operation

Some Points one needs to consider when using Retrieve, Retrieve Multiple Operation for retrieving data from CRM.

Retrieve Operation

Retrieves Only one record using the parameters like Entity Name, GUID, Columns of the record.
Using this we cannot retrieve multiple records or related data.
This will not support for using where clauses.
Where there is a scenario to retrieve single record, with known GUID we could use this approach.

Retrieve Operation

Fig – Retrieve Operation

The below request will return you all the column for the GUID matching record.

 <Retrieve xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
      <entityName>account</entityName>  
      <id>1D30CA6E-E355-E511-80F2-C4346BC5A780</id>  
      <columnSet xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:AllColumns>true</b:AllColumns>  
       <b:Columns xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
         <c:string>name</c:string>  
       </b:Columns>  
      </columnSet>  
   </Retrieve>

Sample XML Retrieve Request

Retrieve Multiple Operation

Retrieve Multiple supports many other options / several overloads like QueryExpression, FetchExpression,QueryByAttribute.
We can use this operation to retrieve multiple, related entity records in a single request using where clauses, with many other parameters filtering options.
However, it has some limitation like single Retrieve Multiple Request can get you some limited records up to 5000, this is configurable in CRM On premise but not in Online.

Retrieve Multiple Operation

Fig – Retrieve Multiple Operation

Sample Retrieve Multiple Request from BizTalk to CRM in two formats FetchExpression, QueryExpression for the same query.

The below sample basic query will return all the entity records with all the columns / attributes specified, maximum entity records can be 5000 as it’s a limit at CRM.

 <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts">  
      <query i:type="b:FetchExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:Query>  
       &lt;fetch version=&quot;1.0&quot; output-format=&quot;xml-platform&quot; mapping=&quot;logical&quot; distinct=&quot;false&quot; &gt;  
      &lt;entity name=&quot;sms_registrationpage&quot;&gt;  
      &lt;attribute name=&quot;sms_registrationpageid&quot; /&gt;  
      &lt;attribute name=&quot;sms_firstname&quot; /&gt;  
      &lt;attribute name=&quot;sms_mobile&quot; /&gt;  
      &lt;attribute name=&quot;sms_lastname&quot; /&gt;  
      &lt;attribute name=&quot;sms_ismarried&quot; /&gt;  
      &lt;attribute name=&quot;sms_gender&quot; /&gt;  
      &lt;attribute name=&quot;sms_emailid&quot; /&gt;  
      &lt;attribute name=&quot;sms_country&quot; /&gt;  
      &lt;attribute name=&quot;sms_age&quot; /&gt;  
      &lt;order attribute=&quot;sms_firstname&quot; descending=&quot;false&quot; /&gt;  
      &lt;/entity&gt;  
      &lt;/fetch&gt;  
       </b:Query>  
      </query>  
    </RetrieveMultiple>

Fig- Retrieve Multiple Using FetchExpression

<RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts">  
      <query i:type="b:QueryExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:ColumnSet>  
         <b:AllColumns>false</b:AllColumns>  
         <b:Columns xmlns:e="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
          <e:string>sms_registrationpageid</e:string>  
          <e:string>sms_firstname</e:string>  
          <e:string>sms_mobile</e:string>  
          <e:string>sms_lastname</e:string>  
          <e:string>sms_ismarried</e:string>  
          <e:string>sms_gender</e:string>  
          <e:string>sms_emailid</e:string>  
          <e:string>sms_country</e:string>  
          <e:string>sms_age</e:string>  
         </b:Columns>  
       </b:ColumnSet>  
       <b:Criteria>  
         <b:Conditions/>  
         <b:FilterOperator>And</b:FilterOperator>  
         <b:Filters/>  
       </b:Criteria>  
       <b:Distinct>false</b:Distinct>  
       <b:EntityName>sms_registrationpage</b:EntityName>  
       <b:LinkEntities/>  
       <b:Orders>  
         <b:OrderExpression>  
          <b:AttributeName>sms_firstname</b:AttributeName>  
          <b:OrderType>Ascending</b:OrderType>  
         </b:OrderExpression>  
       </b:Orders>  
       <b:PageInfo>  
         <b:Count>0</b:Count>  
         <b:PageNumber>1</b:PageNumber>  
         <b:PagingCookie i:nil="true"/>  
         <b:ReturnTotalRecordCount>false</b:ReturnTotalRecordCount>  
       </b:PageInfo>  
       <b:NoLock>false</b:NoLock>  
      </query>  
    </RetrieveMultiple>

Fig- Retrieve Multiple Request Using QueryExpression

CRM Paging Cookie

There can be some scenario where you need to query the CRM records and process the response data records in set of records. You can use the paging cookie feature to make paging in an application faster for large datasets. When you use the paging cookie feature when querying a set of records, the result contains a value for the paging cookie. To improve system performance, you can then pass that value when you retrieve the next set of records.

Scenario 1:

Assume that an entity has 10,000 records and BizTalk has to query to get those records and process individual records within a loop. Here we know that CRM will return maximum of 5000 records in single response. In this case we can split it up with two requests, set 1^st to get 1^st 5000, 2^ndset to get next another 5000 records and then process them in BizTalk. The downside here will be that file with 5000 records will be size approx 300 – 500 MB depending on the attributes selections.

Scenario 2:

Assume, an entity has 10,000 records, BizTalk has to process them individually. Here the requirement is to query a set of 1000 records at a time, process them in BizTalk and then go for next set of batch of records and so on. So BizTalk has to query CRM 10 times to get 1000 records every time to process 10,000 records. Going with this approach there will be less load in BizTalk as we restricted the batch size.

The approach discussed in Scenario 2 is more performance oriented and yield better results.

Demo

CRM Paging Cookie

Fig – CRM Entity Records total 4 records.

Page Cookie using FetchExpression

dynamics crm paging cookie

Fig – Mapping to FetchExpression

<xsl:template name="FetchExpression">  
   <xsl:param name="Count" />  
   <xsl:param name="pageIndex"/>  
   <xsl:param name="pageCookie" />  
   <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
    <query i:type="b:FetchExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
     <b:Query>  
      &lt;fetch version=&quot;1.0&quot; output-format=&quot;xml-platform&quot; mapping=&quot;logical&quot; distinct=&quot;false&quot; count=&quot;<xsl:value-of select="$Count"/>&quot; <xsl:if test="$pageIndex">  
       page="<xsl:value-of select="$pageIndex"/>"  
      </xsl:if> <xsl:if test="$pageCookie">  
       paging-cookie="<xsl:value-of select="$pageCookie"/>"  
      </xsl:if> &gt;  
      &lt;entity name=&quot;sms_registrationpage&quot;&gt;  
      &lt;attribute name=&quot;sms_registrationpageid&quot; /&gt;  
      &lt;attribute name=&quot;sms_firstname&quot; /&gt;  
      &lt;attribute name=&quot;sms_mobile&quot; /&gt;  
      &lt;attribute name=&quot;sms_lastname&quot; /&gt;  
      &lt;attribute name=&quot;sms_ismarried&quot; /&gt;  
      &lt;attribute name=&quot;sms_gender&quot; /&gt;  
      &lt;attribute name=&quot;sms_emailid&quot; /&gt;  
      &lt;attribute name=&quot;sms_country&quot; /&gt;  
      &lt;attribute name=&quot;sms_age&quot; /&gt;  
      &lt;order attribute=&quot;sms_firstname&quot; descending=&quot;false&quot; /&gt;  
      &lt;/entity&gt;  
      &lt;/fetch&gt;  
     </b:Query>  
    </query>  
   </RetrieveMultiple  
 </xsl:template>

Fig – XSLT Used in Scripting

FetchExpression

Fig – End to End Output FetchExpression Request, Response with Count Set to 2

dynamics crm paging cookie

Using Page Cookie using QueryExpression

Mapping to QueryExpression

Fig – Mapping to QueryExpression Request, Response with Count Set to 2

<xsl:template name="QueryExpression">  
   <xsl:param name="Count" />  
   <xsl:param name="pageIndex"/>  
   <xsl:param name="pageCookie" />  
   <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
    <query i:type="b:QueryExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
     <b:ColumnSet>  
      <b:AllColumns>false</b:AllColumns>  
      <b:Columns xmlns:e="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
       <e:string>sms_registrationpageid</e:string>  
       <e:string>sms_firstname</e:string>  
       <e:string>sms_mobile</e:string>  
       <e:string>sms_lastname</e:string>  
       <e:string>sms_ismarried</e:string>  
       <e:string>sms_gender</e:string>  
       <e:string>sms_emailid</e:string>  
       <e:string>sms_country</e:string>  
       <e:string>sms_age</e:string>  
      </b:Columns>  
     </b:ColumnSet>  
     <b:Criteria>  
      <b:Conditions/>  
      <b:FilterOperator>And</b:FilterOperator>  
      <b:Filters/>  
     </b:Criteria>  
     <b:Distinct>false</b:Distinct>  
     <b:EntityName>sms_registrationpage</b:EntityName>  
     <b:LinkEntities/>  
     <b:Orders>  
      <b:OrderExpression>  
       <b:AttributeName>sms_firstname</b:AttributeName>  
       <b:OrderType>Ascending</b:OrderType>  
      </b:OrderExpression>  
     </b:Orders>  
     <b:PageInfo>  
      <xsl:choose>  
       <xsl:when test="$Count">  
        <b:Count>  
         <xsl:value-of select="$Count"/>  
        </b:Count>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:Count>0</b:Count>  
       </xsl:otherwise>  
      </xsl:choose>  
      <xsl:choose>  
       <xsl:when test="$pageIndex">  
        <b:PageNumber>  
         <xsl:value-of select="$pageIndex"/>  
        </b:PageNumber>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:PageNumber>0</b:PageNumber>  
       </xsl:otherwise>  
      </xsl:choose>  
      <xsl:choose>  
       <xsl:when test="$pageCookie">  
        <b:PagingCookie>  
         <xsl:value-of select="$pageCookie"/>  
        </b:PagingCookie>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:PagingCookie i:nil="true"/>  
       </xsl:otherwise>  
      </xsl:choose>  
      <b:ReturnTotalRecordCount>false</b:ReturnTotalRecordCount>  
     </b:PageInfo>  
     <b:NoLock>false</b:NoLock>  
    </query>  
   </RetrieveMultiple>  
  </xsl:template>

Fig – XSLT Used in Scripting

XSLT Used in Scripting

Fig – End to End Output QueryExpression Request, Response with Count Set to 2

End to End Output QueryExpression Request

QueryExpression Request

MSDN Sample Using C#

Sample: Use QueryExpression with a paging cookie: https://msdn.microsoft.com/en-in/library/mt269606.aspx

Sample: Use FetchXML with a paging cookie: https://msdn.microsoft.com/en-us/library/gg328046.aspx

Building the Sample

Follow the below-detailed steps to Run the below sample.

It has some pre requisites if you are checking this article for the 1^st time, Refer the below see also sections articles for related BizTalk artifacts for more details to run this sample End to End.
To download the sample BizTalk Application for using Dynamics CRM Online / On Premise CRM Paging Cookie, Refer here MSDN Code Samples
Zip File Includes: BizTalk Sample Solution, Application Port Bindings, Sample Retrieve Multiple Request, Response files for FetchExpression, QueryExpression.

Building the Sample

Conclusion

In this article you have learn on how to utilize the CRM Online / On premise Paging, Paging Cookie features.
Using this technique , you can minimize the load on BizTalk, when you are working with bulk data processing as Retrieve Response.
You could choose which query expression you wish to go with depending on the scenarios, both are similar the way we write expression is difference.

BizTalk Server – Dynamics CRM Online / On Premise CRM Paging Cookie

Problem Scenario

Solution

Retrieve Operation

Retrieve Multiple Operation

CRM Paging Cookie

Scenario 1:

Scenario 2:

Demo

MSDN Sample Using C#

Building the Sample

Conclusion

See Also