Using paging in Query Expression

There are a lot of case that we need to get thousand of thousand record in one time, but as default, Ms Crm has supported for 5000 record per query. If you want get more than 5000 records, you can use Paging-Cookie.

In fact, we can edit to get more record via configure the web.config, but it is not recommended by Microsoft. 

Step 1: Create 1 console project for testing purpose.

Tip: You can go to Crm Sdk folder “SDK\SampleCode\CS\Client\SOAPLogger”, this project will help to capture Request that you can use for client script later. This post uses it for Crm Connecion 

public void Run(ServerConnection.Configuration serverConfig)
{
	   try
	   {

		// Connect to the Organization service.
			// The using statement assures that the service proxy will be properly disposed.
			using (_serviceProxy = ServerConnection.GetOrganizationProxy(serverConfig))
			{
			 // This statement is required to enable early-bound type support.
			 _serviceProxy.EnableProxyTypes();
			 IOrganizationService service = (IOrganizationService)_serviceProxy;

			 using (StreamWriter output = new StreamWriter("output.txt"))
			 {
			  SoapLoggerOrganizationService slos = new SoapLoggerOrganizationService(serverConfig.OrganizationUri, service, output);

			  string fetchXml = string.Empty;
			  ExecuteMultipleRequest requestWithResults = null;

			  // Define the fetch attributes.
			  // Set the number of records per page to retrieve.
			  int fetchCount = 10;
			  // Initialize the page number.
			  int pageNumber = 1;
			  // Initialize the number of records.
			  int recordCount = 0;
			  // Specify the current paging cookie. For retrieving the first page,
			  // pagingCookie should be null.
			  string pagingCookie = null;
			  bool isContinue = true;</strong>
			 }
			}
		}
}

[/sourcecode language="xml"]
Then, you can define your query, you can use query expression or fetchXml. I will use FetchXml for this article.</pre>

var fetchXml = @"
<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
<entity name='contact'>
<attribute name='fullname' />
<attribute name='parentcustomerid' />
<attribute name='emailaddress1' />
<attribute name='contactid' />
<attribute name='inx_emailinvalid' />
<order attribute='fullname' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='0' />
<filter type='or'>
//My Custom fields - You can change to your own.
<condition attribute='inx_emailinvalid' operator='eq' value='0' />
<condition attribute='inx_emailinvalid' operator='null' />
</filter>
<condition attribute='emailaddress1' operator='not-null' />
</filter>
							<link-entity name='listmember' from='entityid' to='contactid' visible='false' intersect='true'>
							<link-entity name='list' from='listid' to='listid' alias='ab'>
<filter type='and'>
<condition attribute='listid' operator='not-in'>
<value uiname='PA-PH_LAM-Contacts_Portuguese' uitype='list'>{AED5C8AE-BD0B-E611-AFCA-AC162D6F244C}</value>
<value uiname='PAEG Afro Packaging' uitype='list'>{2DF29538-19C0-E511-AF8A-AC162D6F244C}</value>
<value uiname='PANL Inhouse Show 2016 - Contacts Fuhrmeister S.' uitype='list'>{B20B2895-FD18-E611-AFCA-AC162D6F244C}</value>
<value uiname='PPMA Total' uitype='list'>{3AA83B78-0827-E611-8ACD-AC162D6F244C}</value>
</condition>
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>";</pre>
<pre>

Then, you can init the multiple execute request:

 requestWithResults = new ExecuteMultipleRequest()
 {
 // Assign settings that define execution behavior: continue on error, return responses. 
 Settings = new ExecuteMultipleSettings()
 {
 ContinueOnError = false,
 ReturnResponses = true
 },
 // Create an empty organization request collection.
 Requests = new OrganizationRequestCollection();
 };</pre>
while (isContinue)
{
 string xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);
RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest
{
Query = new FetchExpression(xml)
};

EntityCollection returnCollection = ((RetrieveMultipleResponse)slos.Execute(fetchRequest1)).EntityCollection;
}
ExecuteMultipleResponse responseWithResults = (ExecuteMultipleResponse)slos.Execute(requestWithResults);
if (returnCollection.MoreRecords)
{
// Increment the page number to retrieve the next page.
isContinue = true;
pageNumber++;
}
else
{
// If no more records in the result nodes, exit the loop.
isContinue = false;
break;
}
<pre>

The follow function will add paging, page count to your fetchXml

public string CreateXml(string xml, string cookie, int page, int count)
{
StringReader stringReader = new StringReader(xml);
XmlTextReader reader = new XmlTextReader(stringReader);

// Load document
XmlDocument doc = new XmlDocument();
doc.Load(reader);

return CreateXml(doc, cookie, page, count);
}

public string CreateXml(XmlDocument doc, string cookie, int page, int count)
{
XmlAttributeCollection attrs = doc.DocumentElement.Attributes;

if (cookie != null)
{
XmlAttribute pagingAttr = doc.CreateAttribute("paging-cookie");
pagingAttr.Value = cookie;
attrs.Append(pagingAttr);
}

XmlAttribute pageAttr = doc.CreateAttribute("page");
pageAttr.Value = System.Convert.ToString(page);
attrs.Append(pageAttr);

XmlAttribute countAttr = doc.CreateAttribute("count");
countAttr.Value = System.Convert.ToString(count);
attrs.Append(countAttr);

StringBuilder sb = new StringBuilder(1024);
StringWriter stringWriter = new StringWriter(sb);

XmlTextWriter writer = new XmlTextWriter(stringWriter);
doc.WriteTo(writer);
writer.Close();

return sb.ToString();
}</pre>
<pre>

This sample just demonstrate the paging concept in Microsoft Dynamic Crm (all version), depend on your requirement, you can combine with client Script and other technologies. Hope it will be useful for someone.