Dynamics 365 — Retrieve More than 5000 records with FetchXML

 When you’re working with Dynamics 365, you may have encountered a limitation when it comes to retrieving records. The maximum number of records that can be retrieved in a single query is 5.000. This can be a problem if you need to retrieve a large number of records, such as for a data migration or integration project.

 This post will be about retrieveing more than 5000 records by using FetchXML.

Retrieving more than 5000 records using FetchXML in Dynamics 365 involves using the “paging cookie” and the page option in the FetchXML query, which allows you to retrieve a specific set of records and then move to the next set of records until all records have been retrieved.

Here’s an example of how to use the paging cookie option in a FetchXML query:

private static List<Entity> GetAccounts()
	{
	string fetchXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
	<entity name='account'>
	<attribute name='accountnumber' />
	<attribute name='accountid' />
	<attribute name='modifiedon' />
	<order attribute='modifiedon' descending='true' />
	<order attribute='name' descending='false' />
	</entity>
	</fetch>";
	return orgService.RetrieveAll(new FetchExpression(fetchXml));
	}
	public static List<Entity> RetrieveAll(this IOrganizationService service, FetchExpression query)
	{
	var conversionRequest = new FetchXmlToQueryExpressionRequest
	{
	FetchXml = query.Query
	};
	var conversionResponse =
	(FetchXmlToQueryExpressionResponse)service.Execute(conversionRequest);
	return RetrieveAll(service, conversionResponse.Query);
	}
	public static List<Entity> RetrieveAll(this IOrganizationService service, QueryExpression query)
	{
	var result = new List<Entity>();
	var entities = service.RetrieveMultiple(query);
	result.AddRange(entities.Entities);
	var page = 2;
	while (entities.MoreRecords)
	{
	query.PageInfo = new PagingInfo
	{
	PagingCookie = entities.PagingCookie,
	PageNumber = page
	};
	entities = service.RetrieveMultiple(query);
	result.AddRange(entities.Entities);
	page++;
	}
	return result;
	}

In this example, the count attribute is not used so the default record count per page will be 5.000, which is the maximum number of records that can be retrieved in a single query. The PagingCookie attribute contains a cookie that specifies the page number (in this case, page 2) and the first and last record IDs in the set.

To retrieve the next set of records, you would simply increment the page number in the paging cookie and run the query again. This process can be repeated until all records have been retrieved.

Use RetrieveDuplicateRequest in Dynamics CRM

 Introduction:

Recently we had a requirement where we had to create Contact records from data received from other System and if Duplicate record found then avoid creation of records based on Duplicate Detection rules specified in the System and get the List of the Duplicate Records found in the system.

The Duplicate Detection rules might be modified or removed whenever needed in future.

We can avoid creation of records using the CreateRequest but as we wanted to get the Duplicate records too we choose the option to use “RetrieveDuplicatesRequest”.

Walkthrough:

We have Published Duplicate Detection rules for Contact in the System by default created for First Name Last Name, Email address and business phone number as you can see below:In order to create Contacts using the Duplicate Detection rules we follow below steps:

• Suppose in System we already have 2 contacts with First Name: Jo and Last Name: Smith
• Now we received the same data [First Name: Jo, Last Name: Smith] to create Contact in CRM.

So now how we can avoid creation of this record and display the duplicate records information:

Create an Entity object of Contact with all the required attributes as per business requirement firstname and lastname as you can see below:

Entity contactRecord = new Entity("contact");

       contactRecord.Attributes["firstname"] = "Jo";

contactRecord.Attributes["lastname"] = "Smith ";

contactRecord.Attributes["emailaddress1"] = "jo@test.com";

After you have created an Entity object pass it to the RetrieveDuplicatesRequest as below:

var request = new RetrieveDuplicatesRequest

                {
//Entity Object to be searched with the values filled for the attributes to check
                    BusinessEntity = contactRecord,
                   
//Logical Name of the Entity to check Matching Entity
   MatchingEntityName = contactRecord.LogicalName,

                    
                };

                var response = (RetrieveDuplicatesResponse)_service.Execute(request);

This request retrieves all the Duplicate records satisfying the Published Duplicate detection rules specified in the system.

“response.DuplicateCollection.Entities.Count” will give the Duplicate records count found in the system.

If Zero count found then no duplicate records are found in the System and we can simply create the record.

If greater than Zero count found then we can retrieve the Duplicate records from response.DuplicateCollection.

Conclusion:

The RetrieveDuplicatesRequest can be used for detecting duplicate records found using the Duplicate Detection Rules specified in the System.

 

Dynamics 365 RetrieveMultiple Plugin

In Dynamics 365, we have the ability to register a plugin when a retrieve multiple request is executed on an entity. Here we will go through creating a a Retrieve Multiple plugin.

First, create a new class library in Visual Studio:

• Microsoft.Xrm.Sdk

In this example, we will run the RetrieveMultiple when Accounts are displayed. We can invoke this by selecting accounts in a view or running an Advanced Find query. Let’s take a look at Active Accounts:

When running through Advanced Find, we can take a look at the FetchXML of the query. Go to the Advanced Find and select Download FetchXML:

The FetchXML looks like:

We will register our plugin on Post Operation. We will have access to the BusinessEntityCollection. Add the following code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Query;

namespace Carl.Crm.RetrieveMultiple

{

public class AccountMultiple : IPlugin

{

public void Execute(IServiceProvider serviceProvider)

{

Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)

serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

if (context.OutputParameters.Contains("BusinessEntityCollection"))

{

var businessEntityCollection = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];

foreach (Entity e in businessEntityCollection.Entities)

{

// Add code

}

}

else

{

throw new InvalidPluginExecutionException("Error");

}

}

}

}

Strongly sign the assembly and build the solution.

Now, we will register the plugin:

 

The plugin will show as registered:

Now register a new step:

Running this in debug, we can see a list of all account objects is returned:

With each of the key/values of the record:

When running through accounts view, we get a query of type FetchExpression:

When running through Advanced Find, we get a query of type QueryExpression:

Note there is a list of entities that support RetrieveMultiple, with not all supporting both connected to the server and disconnected.

Also note, the plugin will fire even if there are no results returned from the query.

If we change the plugin to run as PreValidation or PreOperation, we do not get the BusinessEntityCollection returned.

PreValidation:

PreOperation: