Recently I struggled a lot on how to correctly implement paging of queries against Dynamics CRM Web API. The API returns no more than 5000 items, and any subsequent items are only available after making another request with prepared parameters. What is usually pretty easy with Graph API (dedicated property with a link to the next page) is a bit clumsy with the older API.
In my scenario, I was trying to combine the paging with the usage of so-called fetchXml syntax. There is some minimal documentation available, but it does not explain all gotchas, and the samples require the usage of external pre-compiled libs (no .NET 5.0 available).
The basic usage of fetchXml is described in the following article: Use FetchXML to query data (Microsoft Dataverse) – Power Apps | Microsoft Docs. In this blog post I present how I did this with C# / NET 5.0, there is also a link to a more complex demo app on my GitHub:
https://github.com/marcinotorowski/dynamics-crm-webapi-demo
Let’s assume we have an endpoint and required OAuth2 token to authorize our request. The first thing to do is to start with the XML for fetch query. There are tons of documents and samples, a sample document may look like that:
<fetch mapping="logical" page="1">
<entity name="audit">
<attribute name="objectid" alias="objectid" />
<attribute name="createdon" value="FormattedValue" />
<link-entity name="systemuser" to="objectid" link-type="inner">
<attribute name="fullname" />
</link-entity>
<filter type="and">
<condition attribute="action" operator="eq" value="64" />
</filter>
</entity>
</fetch>
This essentially returns the list of login audits.
To make a query, strip the XML from spaces and white characters and URL-encode it. Then, assign the value to the URL parameter fetchXml and perform the following query:
https://<name>.crm4.dynamics.com/api/data/v9.1/audits/?fetchXml=%3Cfetch%20mapping%3D%22logical%22%20page%3D%221%22%3E%3Centity%20name%3D%22audit%22%3E%3Cattribute%20name%3D%22objectid%22%20alias%3D%22objectid%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20value%3D%22FormattedValue%22%20%2F%3E%3Clink-entity%20name%3D%22systemuser%22%20to%3D%22objectid%22%20link-type%3D%22inner%22%3E%3Cattribute%20name%3D%22fullname%22%20%2F%3E%3C%2Flink-entity%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22action%22%20operator%3D%22eq%22%20value%3D%2264%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E
This is pretty easy with a few functions from HttpUtility class and XDocument with C#. Then, a standard GET request is performed, (with bearer authorization header). The API replies with JSON that has two imporant properties:
value – contains a JSON array of returned elements@Microsoft.Dynamics.CRM.fetchxmlpagingcookie – contains the pagination cookie if there is at least one page more to come, otherwise the property is not set.
Once the paging cookie is present, we need to take its value, unencode it (warning – it seems it has to be done twice, see source code in GitHub!) and then embed in all subsequent queries. The current page as indicated by fetchXml should be also increased by one.
The relevant code in GitHub repo:
/// <summary>
/// Returns the URL pointing to the next chunk of items.
/// </summary>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>A hot running task that returns the URL to the next page.</returns>
/// <remarks>If this method returns null, then it means that there are no more pages to query.</remarks>
public async Task<Uri> GetNextPageUrlAsync(CancellationToken cancellationToken = default)
{
if (this._parsedJsonResponse == null)
{
await this.ReadContentAsync(cancellationToken).ConfigureAwait(false);
}
var pagingCookie = this._parsedJsonResponse[FetchXmlPagingCookie]?.Value<string>();
if (string.IsNullOrEmpty(pagingCookie))
{
return null;
}
cancellationToken.ThrowIfCancellationRequested();
var pagingCookieResponseHtml = HttpUtility.HtmlDecode(pagingCookie);
var html = XElement.Parse(pagingCookieResponseHtml);
// The cookie is stored in the attribute "pagingcookie"
pagingCookie = html.Attribute(PagingCookieHtmlResponse)?.Value;
// The value must be decoded twice!
pagingCookie = HttpUtility.UrlDecode(pagingCookie);
pagingCookie = HttpUtility.UrlDecode(pagingCookie);
var queryParse = HttpUtility.ParseQueryString(this._request.Query);
// Let's take the current fetchXml from the URL and upgrade it with new cookie information
var currentFetch = queryParse.Get(FetchXmlUrlParam);
if (currentFetch == null)
{
throw new InvalidOperationException("fetchXml must be present in the query at this point.");
}
cancellationToken.ThrowIfCancellationRequested();
var bodyAsXml = XElement.Parse(currentFetch);
bodyAsXml.SetAttributeValue(PagingCookieHtmlRequest, pagingCookie);
var page = int.Parse(bodyAsXml.Attribute(PageHtml)?.Value ?? "0");
bodyAsXml.SetAttributeValue(PageHtml, page + 1);
var newQuery = "?" + string.Join("&", queryParse.AllKeys.Select(paramName =>
{
string escapedName, escapedValue;
if (paramName == FetchXmlUrlParam)
{
// if the parameter is fetchXml, we update it with a new value (encoded).
escapedName = HttpUtility.UrlEncode(paramName);
escapedValue = HttpUtility.UrlEncode(bodyAsXml.ToString(SaveOptions.DisableFormatting));
}
else
{
// for any other URL param just take it as-is.
escapedName = HttpUtility.UrlEncode(paramName);
escapedValue = HttpUtility.UrlEncode(queryParse[paramName]);
}
return $"{escapedName}={escapedValue}";
}));
cancellationToken.ThrowIfCancellationRequested();
// Now build the new URI
var uriBuilder = new UriBuilder(this._request)
{
Query = newQuery
};
return uriBuilder.Uri;
}
And the rest is just a matter of standard parsing of the response stream, in a loop for each page.
while (pageUri != null)
{
Console.WriteLine("{0} Getting page #{1} with no more than {2} entries...", stopWatch.Elapsed, page++, perPage);
using var response = await request.RequestAsync(pageUri);
try
{
var elements = await response.GetElementsAsync<DynamicsAuditLog>();
Console.WriteLine("{0} Received {1} entries.", stopWatch.Elapsed, elements.Count);
allElements.AddRange(elements);
pageUri = await response.GetNextPageUrlAsync();
}
catch (Exception e)
{
Console.WriteLine("{0} Error: {1}", stopWatch.Elapsed, e);
return 1;
}
}
Issues I had
- Most of time spent on trying to understand why the authorization with bearer token does not work (HTTP 401). The problem was with the configuration – in my scenario, I had to make sure that there was a proper assignment of the registered app to an application user. This is done in directly in Dynamics CRM, not in Azure Portal.
- The gotcha: Creation of application user was difficult due to some UI changes introduced in Dynamics CRM. The interface for settings is not obvious to find, and many internet sources claim it was somewhere else than where I actually found it. In my case, to find the UI I had to type the dynamics URL followed by
/Main.aspx as the URL, and from the newly opened panel find my way though settings to set-up the user correctly.
- Paging cookie has to be URL-decoded twice. No idea way.
Links
