Dataverse: How to use RetrieveEntityChangesRequest to synchronize data with externals system

In Dataverse, we have lots of ways to notify the external system about the changes that happened in Dataverse individually (per row data). We have Azure Aware Plug-in, WebHooks, and we also can write a custom plugin that will call HTTP operations. But in the world of Software Architecture, there is always a pro and cons with it. And we need to understand alternative ways to do it. Today, we will learn how to create synchronization data in “bulk” ways with the help of the RetrieveEntityChangesRequest and this official documentation. For the demonstration, we will create an Azure WebJob (that is triggered every minute) to get data from Dataverse and update the SQL Table (in Azure too).

In order to use it, we need to make sure the table’s “Track changes” is already checked (you can use new make.powerapps.com > Solutions > Table > Edit > Edit table properties > click the Advance options > Track changes to checked):

Track changes checked

Go to the portal.azure.com > SQL Server > create the SQL Database with the necessary information (below is the sample from my side):

Create SQL Database

Once the database is created, you need to design the tables that you needed. Below I created a table named “Setting” that will store the entity that is being synced and the last token (which will be explained in the later stage). The next table is the “Contact” table which will store the contact’s data (just several attributes that will be synced over for the sample):

The tables needed for the demo

Create Console Project

Create a new Console project with the below references:

<Project Sdk="Microsoft.NET.Sdk;Microsoft.NET.Sdk.Publish">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.8" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="6.0.1" />
    <PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="6.0.0" />
    <PackageReference Include="Microsoft.Extensions.Hosting" Version="6.0.1" />
    <PackageReference Include="Microsoft.Extensions.Hosting.Abstractions" Version="6.0.0" />
    <PackageReference Include="Microsoft.PowerPlatform.Dataverse.Client" Version="1.0.1" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
  </ItemGroup>
  <ItemGroup>
    <None Update="appsettings.json">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
    <None Update="Settings.job">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
  </ItemGroup>
</Project>

For simplicity, I just created 2 cs files. The Helper.cs file:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.PowerPlatform.Dataverse.Client;

namespace DataverseClient;

internal class AppSettings
{
    public string DataverseConnectionString { get; set; } = "";
    public string SqlConnectionString { get; set; }

}

public class Contact
{
    public Guid Id { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? EmailAddress1 { get; set; }
    public string? Telephone1 { get; set; }
    public bool? IsDeleted { get; set; }
}

public class Setting
{
    public Guid Id { get; set; }
    public string? LastToken { get; set; }
    public string? EntityName { get; set; }
}

public class BlogContext : DbContext
{
    public BlogContext(DbContextOptions options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Setting>().ToTable("Setting");
        modelBuilder.Entity<Contact>().ToTable("Contact");
    }

    public DbSet<Contact> Contacts { get; set; }
    public DbSet<Setting> Settings { get; set; }
}

internal static class Helper
{
    public static IHostBuilder CreateHostBuilder()
    {
        var hostBuilder = new HostBuilder()
            .ConfigureAppConfiguration(builder =>
                builder.AddJsonFile(Directory.GetCurrentDirectory() + "//appsettings.json"))
            .ConfigureServices((context, services) =>
            {
                var configuration =
                    context.Configuration.GetSection("Settings").Get<AppSettings>();

                services.AddScoped<IOrganizationServiceAsync2>(_ =>
                {
                    var client = new ServiceClient(configuration.DataverseConnectionString);
                    return client;
                });

                services.AddDbContext<BlogContext>(o => o.UseSqlServer(configuration.SqlConnectionString));
            });

        return hostBuilder;
    }
}

As you can see in the above code:

  • AppSettings will reflect the appsettings.json file
  • Contact and Setting class defines the table needed
  • BlogContext is the DbContext (for this demo, we will use EntityFrameworkCore)
  • Helper class will be the dependency injection information which for this demo will return DataverseServiceClient and correct BlogContext.

Then the main code (Program.cs) will be:

using DataverseClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;

var builder = Helper.CreateHostBuilder().Build();
var serviceProvider = builder.Services;

var service = serviceProvider.GetRequiredService<IOrganizationServiceAsync2>();
var blogContext = serviceProvider.GetRequiredService<BlogContext>();

var contactSetting = blogContext.Settings.First(e => e.EntityName == "contact");

var validAttributes = new[] { "firstname", "lastname", "emailaddress1", "telephone1" };
var request = new RetrieveEntityChangesRequest
{
    EntityName = "contact",
    Columns = new ColumnSet(validAttributes),
    PageInfo = new PagingInfo { PageNumber = 1, Count = 5000, ReturnTotalRecordCount = false },
    DataVersion = contactSetting.LastToken
};

await blogContext.Database.BeginTransactionAsync();

var token = "";
while (true)
{
    var result = (RetrieveEntityChangesResponse)await service.ExecuteAsync(request);

    // Upsert operations
    var contactUpdates = result.EntityChanges.Changes
        .Where(x => x.Type == ChangeType.NewOrUpdated)
        .Select(x => ((NewOrUpdatedItem)x).NewOrUpdatedEntity).ToArray();
    foreach (var contact in contactUpdates)
    {
        var update = new Contact { Id = contact.Id };
        if (contact.Attributes.ContainsKey("firstname"))
        {
            update.FirstName = contact.GetAttributeValue<string>("firstname");
        }

        if (contact.Attributes.ContainsKey("lastname"))
        {
            update.LastName = contact.GetAttributeValue<string>("lastname");
        }

        if (contact.Attributes.ContainsKey("emailaddress1"))
        {
            update.EmailAddress1 = contact.GetAttributeValue<string>("emailaddress1");
        }

        if (contact.Attributes.ContainsKey("telephone1"))
        {
            update.Telephone1 = contact.GetAttributeValue<string>("telephone1");
        }

        if (await blogContext.Contacts.AnyAsync(e => e.Id == update.Id))
        {
            blogContext.Contacts.Update(update);
        }
        else
        {
            await blogContext.Contacts.AddAsync(update);
        }

        Console.WriteLine($"Upsert Contact {contact.Id}..");
    }

    // Delete operations
    var contactDeletes = result.EntityChanges.Changes
        .Where(x => x.Type == ChangeType.RemoveOrDeleted)
        .Select(x => ((RemovedOrDeletedItem)x).RemovedItem).ToArray();
    foreach (var contactReference in contactDeletes)
    {
        var update = new Contact
        {
            Id = contactReference.Id,
            IsDeleted = true
        };

        if (await blogContext.Contacts.AnyAsync(e => e.Id == contactReference.Id))
        {
            blogContext.Contacts.Update(update);
        }
        else
        {
            await blogContext.Contacts.AddAsync(update);
        }

        Console.WriteLine($"Soft deleted Contact {contactReference.Id}..");
    }


    if (!result.EntityChanges.MoreRecords)
    {
        token = result.EntityChanges.DataToken;
        break;
    }

    request.PageInfo.PageNumber++;
    request.PageInfo.PagingCookie = result.EntityChanges.PagingCookie;
}

if (!string.IsNullOrEmpty(token))
{
    contactSetting.LastToken = token;
    blogContext.Settings.Update(contactSetting);
}

await blogContext.SaveChangesAsync();
await blogContext.Database.CommitTransactionAsync();

Console.WriteLine("Done..");

The main point here is when we execute RetrieveEntityChangesRequest without DataVersion attribute, the system will return all the changes that happening from the beginning. That is why the data can be so huge for the first time (need to use while-loop + PageInfo). When we got the data, we have two possibilities: NewOrUpdatedItem or RemovedOrDeletedItem which in the above code will run different logic. If no data that can be retrieved again, we will pass the result.EntityChanges.DataToken to the token variable. This token will be important to identify in which part we have already synced the data to the external source system. That is why when we execute the RetrieveEntityChangesRequest, we also set the DataVersion with the token that we store in Setting.LastToken.

Once everything is okay, we just need to save the changes in the SQL Table (for this part I’ll not explain it in detail as the code is pretty simple 😁).

For the appsettings.json, here is the sample code (change it based on your environment):

{
  "Settings": {
    "DataverseConnectionString": "AuthType=OAuth;Username=[username];Password=[password];Url=https://[orgid].crm.dynamics.com/;AppId=51f81489-12ee-4a9e-aaae-a2591f45987d;RedirectUri=app://58145B91-0C36-4500-8554-080854F2AC97",
    "SqlConnectionString": "Server=tcp:[db-url].database.windows.net,1433;Initial Catalog=[db-name];Persist Security Info=False;User ID=[user];Password=[password];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }
}

Last, for the Settings.job (you can change the setting based on what you need using the cron command in this documentation):

{
  "schedule": "0 * * * * *"
}

Next, you can test in your local machine. Here is the local result (I need to create the Setting row manually using SSMS):

Once everything is okay, you can create Publish Profile (which also will help you to create the App Service and App Service Plan during the setup):

Create Publish Profile

Once you publish, you can start updating your Contact‘s data, and below is the result:

WebJob running every minute and updating the Contact’s table data

Happy CRM-ing!

Advertisement

3 thoughts on “Dataverse: How to use RetrieveEntityChangesRequest to synchronize data with externals system

  1. Hi,

    Seeking some guidance how incremental data from dataverse can be ingested to external enterprise data hub?

    Data hub supports API integration , also both push and pull. Push is the primary way in which Producer of the data will invoke API and ingest. However with my limited knowledge cannot find any out-of-box capability provided by Dataverse?

    How dataverse web APIs can be scheduled to push data to external system?

    Dataverse weblink only allow integration with Azure synapse or ADF.

    External system is Amazon S3. automate connector can do but in ingress direction not otherway? Any way to achieve this without using 3rd party services like precog or cdata.

    Like

    1. I don’t have the experience implementing the scenario that you want using third-party libs. We have Virtual Entity but this is still limited to getting data from SQL Server.
      But if you want to do some customization what I can think:
      – For pushing data from Dataverse to External System, you can plug in using Azure Aware Plugin (https://learn.microsoft.com/en-us/power-apps/developer/data-platform/write-custom-azure-aware-plugin?WT.mc_id=DX-MVP-5004571) or you also can create Post Async plugin that will be called WebApi which will call external system API.
      – Then for the pull of the changes from the external system to Dataverse, you can have WebAPI that will be called from the external system + implement DataverseServiceClient to update (https://github.com/microsoft/PowerPlatform-DataverseServiceClient).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.