UpsertRequest for Data Synchronization

Mastering Data Synchronization with UpsertRequest in Dataverse

Introduction: The Challenge of Data Consistency

In the world of enterprise architecture, keeping data consistent across disparate systems is a fundamental challenge. Whether you are synchronizing customer information from an external ERP, importing lead data from a marketing platform, or migrating records from a legacy database, you inevitably face the "Create vs. Update" dilemma. If a record already exists, you must update it; if it does not exist, you must create it.

Manually checking for the existence of a record before deciding to perform a Create or Update operation creates significant overhead. You have to query the database, parse the response, verify if the record is found, and then execute the subsequent action. This pattern, often called "Check-then-Act," introduces latency, increases the number of API calls, and risks race conditions where two processes attempt to create the same record simultaneously.

The UpsertRequest in Microsoft Dataverse is the solution to this problem. It is a powerful, atomic operation that combines the logic of creation and updating into a single request. By providing a unique identifier or an alternate key, you instruct Dataverse to handle the decision-making process for you. Understanding how to use UpsertRequest effectively is essential for any developer looking to build high-performance, resilient integration patterns within the Power Platform ecosystem.

Understanding the Upsert Mechanism

At its core, an Upsert (a portmanteau of "Update" and "Insert") operation is a single API call that performs a "smart" write. When you send an UpsertRequest to Dataverse, the platform evaluates the provided record data. If the record identified by your unique key already exists in the system, Dataverse performs an update on the fields you have provided. If the record does not exist, Dataverse creates a new record using those same fields.

The primary benefit of this approach is atomicity and efficiency. Because the check-and-write logic happens entirely within the Dataverse engine, you eliminate the "round-trip" time associated with checking for the record's existence from your client application. This significantly reduces the total number of API calls, which is critical when working within the strict service protection limits imposed by the Dataverse API.

Callout: Atomic Operations vs. Client-Side Logic In traditional programming, an upsert is often implemented as:

1. Query for record ID.
2. If ID exists, Update.
3. If ID is null, Create. This approach is inefficient because it requires two network trips. The UpsertRequest moves this logic to the server side, turning two potential network calls into one, ensuring that the operation is handled as a single transaction by the database engine.

Prerequisites for Using UpsertRequest

Before you can use UpsertRequest, your target entity must have a way for Dataverse to identify a "matching" record. You cannot simply tell Dataverse to "upsert this person" without specifying which field represents the unique identity of that person.

The Role of Alternate Keys

While the Primary Key (GUID) of a record is the most obvious way to identify it, integrations often rely on business-centric identifiers like an Email Address, a Customer Number, or a Government ID. These are known as Alternate Keys. To use an UpsertRequest based on these business keys, you must define them within the Dataverse entity configuration.

1. Define the Key: In the Power Apps maker portal, navigate to your table, select the "Keys" section, and create a new key.
2. Unique Constraint: Ensure the key is marked as unique. Dataverse will automatically create a database index for this key, which ensures that lookups are extremely fast.
3. Activation: Once created, the key must be published. It may take a few minutes for the system to index the existing data before the key becomes active for use in operations.

Warning: Performance Considerations While alternate keys are powerful, do not over-index your tables. Every alternate key you define adds a unique index to the underlying SQL table. While this makes reads and upserts faster, it can slightly slow down bulk insert operations because the database must check for constraint violations on every write.

Implementing UpsertRequest in C#

When working with the Dataverse SDK (specifically the Microsoft.Xrm.Sdk assembly), the UpsertRequest class provides a clean, strongly-typed way to perform this operation.

Basic Code Example

The following snippet demonstrates how to perform an upsert on a Contact record using an alternate key named new_customer_id.

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;

// Assume 'service' is an IOrganizationService instance
Entity contact = new Entity("contact", "new_customer_id", "CUST-12345");
contact["firstname"] = "Jane";
contact["lastname"] = "Doe";
contact["emailaddress1"] = "jane.doe@example.com";

UpsertRequest request = new UpsertRequest
{
    Target = contact
};

UpsertResponse response = (UpsertResponse)service.Execute(request);

if (response.RecordCreated)
{
    Console.WriteLine("New record created with ID: " + response.Target.Id);
}
else
{
    Console.WriteLine("Existing record updated.");
}

Breakdown of the Code

• Entity Initialization: Notice that we initialize the Entity object with three parameters: the logical name (contact), the schema name of the alternate key (new_customer_id), and the value of that key (CUST-12345). This tells Dataverse, "Here is the record, and here is how you find it."
• The Request: We instantiate UpsertRequest and assign our entity to the Target property.
• The Response: The Execute method returns an UpsertResponse. This is incredibly useful for logging and auditing, as it contains the RecordCreated boolean, allowing your code to branch logic based on whether a new record was added or an existing one was modified.

Advanced Scenarios: Handling Complex Relationships

In real-world integrations, you are rarely dealing with flat, single-entity data. You often need to relate records during an upsert (e.g., upserting a Contact and associating them with an Account).

Upserting with Lookups

When you include a lookup field in your UpsertRequest, you must ensure that the related record exists. If you are upserting a Contact and setting the parentcustomerid, you can use the same alternate key pattern to reference the parent account.

Entity contact = new Entity("contact", "new_customer_id", "CUST-12345");
contact["firstname"] = "Jane";

// Set lookup to an Account using the Account's alternate key
Entity accountReference = new Entity("account", "accountnumber", "ACC-999");
contact["parentcustomerid"] = accountReference.ToEntityReference();

UpsertRequest request = new UpsertRequest { Target = contact };
service.Execute(request);

By passing an EntityReference that contains the alternate key rather than a GUID, you avoid having to perform a separate query to resolve the Account's GUID. This is a highly efficient way to handle relational data synchronization.

Tip: Handling Multiple Keys If you have multiple alternate keys defined for an entity, Dataverse will attempt to use the key provided in the constructor. If you provide a key that does not exist or is not valid for that entity, the operation will throw a FaultException. Always validate your key names in the Power Apps portal before writing integration code.

Upserting via Web API (REST)

For developers working in non-.NET environments—such as Node.js, Python, or even Power Automate—the Dataverse Web API is the preferred interface. The Web API handles upserts using the PATCH method with the If-Match and If-None-Match headers, or more simply, by using the Upsert pattern provided by the API.

Using the Web API

To upsert a record via the Web API, you perform a PATCH request to the entity set. If you include the alternate key in the URL, Dataverse performs the upsert logic.

Request: PATCH [Organization URI]/api/data/v9.2/contacts(new_customer_id='CUST-12345')

Payload:

{
    "firstname": "Jane",
    "lastname": "Doe",
    "emailaddress1": "jane.doe@example.com"
}

If the record exists, it updates. If it does not exist, the API will create it. This is a clean, RESTful way to manage synchronization without needing the heavy SDK.

Comparison: Upsert vs. Create vs. Update

It is helpful to understand exactly when UpsertRequest is the right tool for the job. The following table compares the operational characteristics.

Best Practices for Data Synchronization

1. Idempotency is Key

Your synchronization processes should be idempotent. This means that if you run the same integration process twice, the end result in Dataverse should be the same as if you ran it once. UpsertRequest is inherently idempotent because it doesn't matter how many times you send the same data; the final state of the record in Dataverse remains the same.

2. Logging and Auditability

Because UpsertRequest can result in either a create or an update, your integration logs should capture the RecordCreated boolean from the response. This allows you to generate reports on how much "new" data is entering your system versus "existing" data being refreshed.

3. Handle Large Batches with Change Tracking

If you are synchronizing thousands of records, do not send them one by one. Use the ExecuteMultipleRequest or the Web API's batch processing capabilities. Furthermore, utilize Dataverse Change Tracking to only fetch records from the source system that have changed since the last synchronization, then apply those changes using UpsertRequest.

4. Field Mapping

Be cautious when mapping fields. If you are upserting a record, you might be unintentionally overwriting fields that should remain untouched. For example, if your integration only provides a name and email, make sure you are not clearing out other fields (like a phone number) that were previously populated in Dataverse.

Callout: Avoiding the "Nulled-Out" Trap When performing an Upsert (or an Update), Dataverse will update any field you include in the entity object. If you include a field but set its value to null, Dataverse will clear that value in the database. If your goal is only to update specific fields, only add those specific attributes to your Entity object.

Common Pitfalls and Troubleshooting

Pitfall 1: Key Collisions

If your external system has poor data quality and you attempt to upsert multiple records with the same alternate key, Dataverse will throw an error because the key must be unique. You must sanitize your data before the upsert operation.

Pitfall 2: Incorrect Key Names

A common mistake is using the display name of a field instead of the logical name (schema name). For example, if your field is "Customer ID", the logical name is likely new_customerid. Using the display name will result in a "Property not found" error. Always verify the schema name in the Power Apps table editor.

Pitfall 3: Not Using Transactions

If you are performing multiple upserts that are logically linked (e.g., an Order and its Order Lines), wrap your operations in a ExecuteTransactionRequest. This ensures that if one upsert fails, the entire batch rolls back, preventing partial data synchronization which is notoriously difficult to clean up.

Troubleshooting Steps:

1. Check the Exception: Catch the FaultException and inspect the Message. It will usually tell you exactly which key failed or which constraint was violated.
2. Verify the Key: Go to the table in the Power Apps portal and ensure the Alternate Key is "Active" and the index status is "Active."
3. Check Permissions: Ensure the user account performing the synchronization has Create and Write privileges on the entity. If the user only has Write access, the Upsert will fail when it attempts to create a new record.

Step-by-Step: Building a Sync Process

If you are tasked with building a sync from a CSV file to a Dataverse Contact table, follow this workflow:

1. Preparation:
   • Define an Alternate Key on the Contact table (e.g., external_id).
   • Publish the changes.
2. Mapping:
   • Create a mapping file that translates your CSV headers to the Dataverse logical names.
3. Development:
   • Read the CSV line-by-line.
   • Create an Entity object for each row.
   • Set the keyname and keyvalue in the constructor.
   • Map the remaining columns to the entity's attributes.
   • Execute the UpsertRequest.
4. Error Handling:
   • Wrap the execution in a try-catch block.
   • Log failures to a local file or an error-tracking table.
5. Verification:
   • Use the RecordCreated property to update your own local audit log.

Comparing UpsertRequest with Create and Update logic

Many developers struggle with the decision of whether to use Upsert or to write explicit Retrieve -> Update/Create logic. The explicit logic approach is only necessary if you need to perform complex business logic in between the check and the write. For example, if you need to perform a calculation based on the current value in the database before deciding whether to update, you must use the standard retrieval flow. However, for 90% of data synchronization scenarios, the UpsertRequest is superior due to its simplicity and performance.

Key Takeaways

1. Atomicity is Efficiency: UpsertRequest reduces the overhead of integration by combining the "check" and "action" into a single server-side operation, minimizing network latency and API usage.
2. Alternate Keys are Mandatory: You cannot perform an upsert without a unique identifier. Investing time in defining robust alternate keys is the foundation of a reliable synchronization strategy.
3. Leverage the Response: The UpsertResponse object is an underutilized resource. Always check the RecordCreated flag to gain insights into your data synchronization patterns and improve your logging.
4. Use Transactions for Relationships: When syncing relational data, use ExecuteTransactionRequest to ensure data integrity across related tables, preventing partial updates that lead to "orphaned" records.
5. Mind the Schema Names: Always use logical schema names, not display names, when interacting with the SDK. Misnaming a field is the most common cause of integration failures.
6. Idempotency is a Goal: Design your integrations to be repeatable. By using UpsertRequest, you naturally move toward an idempotent architecture, making your system more resilient to network failures and re-run scenarios.
7. Performance Matters: Avoid the "Nulled-Out" trap by only including the fields you intend to modify. When dealing with bulk data, combine UpsertRequest with batch processing to stay within API limits while maintaining performance.

By mastering the UpsertRequest, you transition from writing fragile, "check-then-act" code to building resilient, professional-grade integrations that scale with your organization's needs. This tool is a cornerstone of the Dataverse developer's toolkit, and its correct application is a hallmark of a well-architected solution.