Connect – Microsoft Dynamics 365

 

Using LINQ to bulk update Dynamics 365

Jun 04, 2020

By Rod McBride

A common task when working with Dynamics 365 Sales (D365) is the bulk update or insert of data, so I thought I’d put together a LINQ tutorial to help walk you through the process.

In a recent project of ours, a new Contact field (wip_resourcefilter) needed to be added and populated with values to allow a separate FetchXML process to easily filter the records.

In this case, the source data was a comma-separated value (CSV) file containing the ID and Name of the contact records to update in D365, as well the new Resource Filter field, which was null (see Figure 1).

Contacts text file snippet

Figure 1. Contacts text file snippet


A simple approach was to leverage the CRM SDK and LINQ to handle the update. CRM does have a maximum request limit of 1,000, so I’ll work with that limitation in mind.

For this process, I created a LINQPad script, but you could use Visual Studio, VS Code, Rider or any IDE that supports the .NET Framework. I then included the CRM SDK assemblies (Microsoft.CrmSdk.CoreAssemblies and Microsoft.CrmSdk.XrmTooling.CoreAssembly) and logic to read and parse the CSV file (See Listing 1).


var batchSize = 1000;

var optionA2M = 908980001;

var optionN2Z = 908980002;

var listA2M = new List<string> { "a""b""c""d""e""f""g""h""i""j""k""l""m" };

 

// Read and parse CSV file into a collection using LINQ

var contacts = File.ReadAllLines(@"c:\temp\contacts.csv")

                .Skip(1)

                .Select(lines => lines.Split(','))

                .Select(array => new

                {

                    Id = Guid.Parse(array[0]),

                    Name = array[1],

                    ResourceFilter = listA2M.Contains(array[1].ToLower().Substring(01)) 

                                     ? optionA2M : optionN2Z

                })

                .ToList();

Listing 1. Logic to load and process the CSV file


Load the source data

The lines from the CSV file are loaded via the ReadAllLines method, and then LINQ is used to perform the following steps:

  1. Skip the first line from the file, the column headers.
  2. Select the lines and Split each on the comma separator, transforming the results into an array.
  3. Select the arrays and transform each into a new anonymous object with an ID, Name and ResourceFilter property.
    • ID: The first element of the array from step 3.
    • Name: The second element of the array.
    • ResourceFilter: The first character of the Name field is extracted and compared to the listA2M collection to determine which OptionSet value to use/assign (See Figure 2).

    Resource Filter field: OptionSet

    Figure 2. Resource Filter field: OptionSet


    This filter is used to switch the data into two groups: 1) Contact with first names between A and Z, and 2) first names between N and Z.

  4. Returns a collection of anonymous objects (See Figure 3 in the following section).

Update D365

The next step is to update the Contacts in D365 with the Resource Filter value that was set above. However, given the request limitation, when dealing with a large amount of data, I need to break the update into batches of 1,000 requests or fewer. To create the batches, a small LINQ extension method (Batch) was used (see Listing 2).

static class CollectionExtensions

{

    public static IEnumerable<IEnumerable<T>> Batch<T>(this IEnumerable<Tsourceint batchSize)

    {

        return source

        .Select((xi) => new { Index = iValue = x })

        .GroupBy(x => x.Index / batchSize)

        .Select(x => x.Select(v => v.Value).ToArray());

    }

}

Listing 2. Batch Extension Method


Given a batchSize, the batch method will take a collection and break it into smaller subsets. What’s returned from the method is a nested collection with the inner collection containing the batches. (See Figure 3.)

Sample results from the batch method with batchSize = 5

Figure 3. Sample results from the batch method with batchSize = 5


It uses LINQ’s Select method to first index/number each contact, then uses the GroupBy method to “batch” the data based on the specified batch size and transform the batched results into arrays. It’s these smaller arrays, which adhere to the request limit, that are then used.

To update D365, an OrganizationServiceContext is created along with an ExecuteMultipleRequest to bulk load the data, improving the overall performance. The nested collection is then iterated, and each inner batch is also iterated, queuing up a Contact entity with the values from the CSV file. Each new entity is added to the ExecuteMultipleRequest object and executed to update D365. (See Listing 3.)

using (var context = new OrganizationServiceContext(new CrmServiceClient(connectionString)))

{

    // Batch the contacts and update Dynamics

    foreach (var batch in contacts.Batch(batchSize))

    {

 // Define ExecuteMultipleRequest Collection

 var multipleRequest = new ExecuteMultipleRequest()

 {

    Settings = new ExecuteMultipleSettings() { ContinueOnError = falseReturnResponses = true },

    Requests = new OrganizationRequestCollection()

 };

        foreach (var item in batch)

        {

            var entity = new Entity("contact"item.Id

            { 

                ["wip_resourcefilter"] = new OptionSetValue(item.ResourceFilter

            };

            var updateRequest = new UpdateRequest { Target = entity };

            multipleRequest.Requests.Add(updateRequest);

        }

 

        var multipleResponse = (ExecuteMultipleResponse)context.Execute(multipleRequest);

 

        multipleResponse.IsFaulted.Dump("Any batch errors?");

    }

}

Listing 3. Update Dynamics


With these few lines of code, I can easily swap out the entity and/or field(s) to update, which can handle any number of records within reason and re-run as needed. This example used a CSV file as the data source, but the data source could have been a database, a web service or cloud storage.

If you have any questions about LINQ bulk updates in Microsoft Dynamics 365 or other helpful LINQ methods, contact Wipfli. You can also read more technology-focused articles here, including how to filter a list using another list with LINQ.

Figure 4. Snippet: Contacts before update

Figure 4. Snippet: Contacts before update


Figure 5. Snippet: Contacts after update

Figure 5. Snippet: Contacts after update

Comments

*User Name field is required.

(will not be published)

*Real Name field is required.

(will not be published)

*A valid email is required.

*Company field is required.

*Comment field is required.
D365 blog
Subscribe to Connect – Microsoft Dynamics 365

Submit