C# – Power Platform & Dynamics 365

Total Number of Record : RetrieveTotalRecordCount

A very common request by customer is to get total number of record in D365 system for a given entities. It is pretty straight forward for D365 On Prem, you can do a count on SQL DB. But for D365 online you either need to rely on XrmToolBox fetch counter(which can still be used) or aggregrate function or even on DES database. However we do have a simple way now : RetrieveTotalRecordCount

I have all the entities in my system stored in a text file. The C# code to get count of all the entities will be like this:

            string[] entitylist = File.ReadLines(@"D:\entitylist.txt").ToArray();
            var client = new CdsServiceClient(connectionString);// I am using .NET core. Change it to var client = new CrmServiceClient(connectionString);           
            RetrieveTotalRecordCountRequest req = new RetrieveTotalRecordCountRequest
            {
                EntityNames = entitylist
            };
            EntityRecordCountCollection m = ((RetrieveTotalRecordCountResponse)client.Execute(req)).EntityRecordCountCollection;
            long count = 0;
            foreach (var i in m)
            {
                Console.WriteLine(i.Key + " =" + i.Value);
                count += i.Value;
            }
            Console.WriteLine(@$"Count ={count}");

I was having around 600 entitites with 200 million records in total and it took 2 second to get the result. So pretty sure its doing SQL count underneath.

If you are using POSTMAN – just use below url

GET {{webapiurl}}/RetrieveTotalRecordCount(EntityNames=[‘contact’,’account’])

and it ifs JS then just change pass the same request as above using webAPI.

function GetTotalCount(executionContext)
{
	var formContext = executionContext.getFormContext();
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/RetrieveTotalRecordCount(EntityNames="+JSON.stringify(['contact','account']) +")", true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    req.onreadystatechange = function() {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                var results = JSON.parse(this.response);
                for (var i = 0; i < results.EntityRecordCountCollection.Count; i++) {
                    var value = results.EntityRecordCountCollection.Values[i];
                    alert(results.EntityRecordCountCollection.Keys[i] + " = "+ value);
                }
            } else {
                Xrm.Utility.alertDialog(this.statusText);
            }
        }
    };
    req.send();
}

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/retrievetotalrecordcount?view=dynamics-ce-odata-9

We still dont have a way to apply a filter and then get the count directly. Hoping to get this feature in future.

Dynamics 365 Data Migration for Audit Field

The one common question that customer ask when migrating a system to D365 is about these audit field – how can we populate these values.

These are D365 audit field and its not straight forward to fill them with.

CreatedOn

This is most commanly used field since most of the system like to maintain the actual created on date of record rather than the migrated one and that is why we have OOB support for this. There are two way to fill this:

OOB way of overridencreatedon
Plugin

D365 does provide a OOB to fill in the value in this field – but to fill this in we need to map overriddencreatedon field. Once a value is mapped to this field, it in turn populate CreatedOn. While the actual createdon date goes to OverriddenCreatedOn – as the decription of the field says :

CrmServiceClient client = new CrmServiceClient(connectionString);
Entity contact = new Entity("contact")
{
   Attributes = new AttributeCollection
   {
       new KeyValuePair<string, object>("lastname", "override created date"),
       new KeyValuePair<string, object>("overriddencreatedon", DateTime.Now.AddYears(-5))                    
   }
};
var res = client.Create(contact);

If however you use a pre-create/update plugin then the value createdon can be directly populated.

var target = this.PluginExecutionContext.InputParameters.ContainsKey("Target")
                               ? this.PluginExecutionContext.InputParameters["Target"] as Entity
                               : null;
if (PluginExecutionContext.MessageName.ToLower() == "create")
{
      target.Attributes["createdon"] = DateTime.Now.AddYears(-5);
}

CreatedBy, ModifiedBy

Coming to CreatedBy, ModifiedBy. There are 2 ways to fill in these values:

Impersonation
Plugin

So if you fill in the CallerId field of your client object as GUID of the user then these values can be set to that user.

CrmServiceClient client = new CrmServiceClient(connectionString);
client.CallerId = Guid.Parse("{1DBE94FB-BC4E-EB11-A813-000D3A9BF8FB}");// David So
Entity contact = new Entity("contact")
{
    Attributes = new AttributeCollection
    {
        new KeyValuePair<string, object>("lastname", "User Impersonation")
    }
};
var res = client.Create(contact);
client.CallerId = Guid.Parse("{8AEBAA01-BD4E-EB11-A813-000D3A9BF8FB}");//Renee Lo
client.Update(new Entity("contact")
{
    Id = res
});

Please note that the Created By(delegate) and Modified By(delegate) is the actual user on whose name the service object was created. We cannot overwrite these using application running outside CRM – like Console or SSIS.

The other way is to create a pre-create/update plugin and fill in the values.

    var target = this.PluginExecutionContext.InputParameters.ContainsKey("Target")
                               ? this.PluginExecutionContext.InputParameters["Target"] as Entity
                               : null;
    if (PluginExecutionContext.MessageName.ToLower() == "create")
    {
        target.Attributes["createdby"] = new EntityReference("systemuser", Guid.Parse("{1DBE94FB-BC4E-EB11-A813-000D3A9BF8FB}"));//David So
        target.Attributes["modifiedby"] = new EntityReference("systemuser", Guid.Parse("{3BBE94FB-BC4E-EB11-A813-000D3A9BF8FB}")); //Eric Gruber
        target.Attributes["createdonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{8AEBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Renee Lo
        target.Attributes["modifiedonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{C6EBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Mollly Clark
    }
    if (PluginExecutionContext.MessageName.ToLower() == "update")
    {
        target.Attributes["createdby"] = new EntityReference("systemuser", Guid.Parse("{4AEBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Jeff Hay
        target.Attributes["modifiedby"] = new EntityReference("systemuser", Guid.Parse("{B3BD94FB-BC4E-EB11-A813-000D3A9BF8FB}")); //Alicia Thomber
        target.Attributes["createdonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{B7BD94FB-BC4E-EB11-A813-000D3A9BF8FB}"));//Amy Albert
        target.Attributes["modifiedonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{6CEBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Karen Berg
    }

Same thing happens even on pre-update :

Do note thast plugin allow us to override even the delegate field however it isnt ideal to do that. Also updating modified by and modified on is also not encouraged since these value are suppose to updated by users modifying it and programtically updating it can cause loss of this information.

ModifiedOn

The last one is ModifiedOn – The only way is the plugin route. Create a pre-update/pre-create plugin and fill in the propertybag with required valu and you will be good to go.

var target = this.PluginExecutionContext.InputParameters.ContainsKey("Target")
                               ? this.PluginExecutionContext.InputParameters["Target"] as Entity
                               : null;
if (PluginExecutionContext.MessageName.ToLower() == "create")// or update
{
      target.Attributes["modifiedon"] = DateTime.Now.AddYears(-4);
}

A table summarizing these is as follow:

Field	Out Of Box	Pre Create/Update Plugin	Impersonation
CreatedOn	OverriddenCreatedOn	Yes	NA
CreatedBy	No	Yes	Yes
ModifiedOn	No	Yes	NA
ModifiedBy	No	Yes	Yes
CreatedOnBehalfBy	No	Yes	No
ModifiedOnBehalfBy	No	Yes	No

Using .NET Core/MSAL.NET to consume D365 Web API

We can use .NET Core and MSAL.NET library to connect to D365 WebAPI. Following package need to be installed for MSAL.NET library

We are using below configuration for our registered app

Below is the sample code for the getting top 10 accounts. We are using Interactive Mode to get the token :

using Microsoft.Identity.Client;
using System;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;

namespace D365ConnectionUsingMSAL
{
    class Program
    {
        static async Task Main(string[] args)
        {
            string serviceUri = "https://xxxxxxxx.crm.dynamics.com/";
            string apiVersion = "9.1";
            string crmapiUrl = $"{serviceUri}/api/data/v{apiVersion}/accounts";
            string clientId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            string tenantId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

            var publicClient = PublicClientApplicationBuilder
                .Create(clientId)
                .WithAuthority(new Uri($"https://login.microsoftonline.com/{tenantId}"))
                .WithRedirectUri("http://localhost")
                .Build();

            var scope = new string[] { "https://xxxxxxxxx.crm.dynamics.com/.default" };
            AuthenticationResult result = await publicClient.AcquireTokenInteractive(scope).ExecuteAsync();
            using (HttpClient httpClient = new HttpClient())
            {
                httpClient.Timeout = new TimeSpan(0, 2, 0); 
                httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
                var data = await httpClient.GetAsync("https://orgb73380c8.crm.dynamics.com/api/data/v9.1/accounts?$select=name,address1_city&$top=10");
                if (data.StatusCode == System.Net.HttpStatusCode.OK)
                {
                    Console.WriteLine(await data.Content.ReadAsStringAsync());
                }
                else
                {
                    Console.WriteLine($"Some thing went wrong : {data.StatusCode} ");
                }
                Console.ReadLine();
            }
        }
    }
}

We can also use below if you have client secret for the registered app.

var confidentialClient = ConfidentialClientApplicationBuilder.Create(clientId: clientId)
.WithClientSecret(secret)
.WithAuthority(new Uri($"https://login.microsoftonline.com/{tenantId}"))
.Build();