Dataverse Alternate Keys and Upsert: Engineering Idempotent Integrations with C#

Designing reliable record synchronization using alternate keys, Dataverse SDK operations, Web API addressing, and explicit concurrency controls.

Power Platform Engineering Academy — Five Years in Five Days

Technical domain: Microsoft Dataverse
Application: APP-004 — Alternate Key & Upsert Lab
Technologies: Dataverse, C#, Dataverse SDK, Web API, Model-Driven Apps
Status: Pre-implementation engineering design

Introduction

Enterprise integrations rarely operate in perfectly controlled conditions.

Messages can be delivered more than once. HTTP requests can time out after a server has successfully committed a change. External systems may identify records using business keys rather than Dataverse GUIDs. Synchronization processes may restart halfway through a batch.

These conditions create a fundamental engineering problem: How can an integration reliably identify and update the same Dataverse record without creating duplicates?

Microsoft Dataverse provides two closely related capabilities that address important parts of this problem: alternate keys and Upsert.

An alternate key defines a unique identifier based on one or more table columns. Upsert combines insert and update semantics into a single operation, allowing the platform to create a record when no matching record exists or update an existing record when one does.

Together, they provide a powerful foundation for data synchronization.

However, they do not automatically solve every reliability problem.

An Upsert operation is not equivalent to distributed exactly-once processing. A unique key does not prevent stale updates. Retrying an operation is not necessarily safe when the operation changes values based on their previous state.

APP-004 investigates these distinctions through a controlled engineering laboratory built with C#, Dataverse, and a Model-Driven App.

Our central question is:

Under which conditions can alternate keys and Upsert make an integration repeatable, and where are additional concurrency and reliability mechanisms required?

1. Record Identity in Dataverse

Every Dataverse record has a primary identifier represented by a GUID.

For example, a custom table might have the following primary key:

pea_externalcustomerid

A typical SDK update identifies the target record by its primary ID:

var customer = new Entity(
    "pea_externalcustomer",
    customerId);
customer["pea_name"] = "Contoso";
service.Update(customer);

This works when the integration already knows the Dataverse GUID.

External systems frequently do not.

An ERP might identify a customer using an account number. A SharePoint integration might use a document identifier. A legacy database might use a composite key consisting of a source-system identifier and a business code.

Storing a mapping between every external identifier and every Dataverse GUID is possible, but it introduces another synchronization dependency.

Alternate keys allow Dataverse to identify records through selected column values.

2. What Is an Alternate Key?

An alternate key is a table metadata definition that identifies records using one or more columns.

Consider:

A composite alternate key could use:

pea_sourcesystem
+
pea_externalcode

This allows the combination of source system and external code to identify a record.

The same external code may legitimately exist in two different systems:

ERP01 + CUST-10042
CRM02 + CUST-10042

The composite key prevents those records from being confused.

2.1 Alternate keys versus primary keys

Alternate keys are especially useful when integrating systems with established identifiers.

They should not be created merely because a column appears unique in a small sample dataset.

The selected values must represent a stable identity contract.

3. Alternate Key Constraints

Alternate keys have documented platform limitations.

Microsoft documents limits including a maximum of 10 alternate keys per table, a maximum of 16 columns per key, and a maximum key size of 900 bytes.

Not every Dataverse column type is eligible for an alternate key.

Supported types include selected text, numeric, date/time, lookup, and choice columns, subject to platform constraints.

There are additional considerations:

• Null values in alternate-key columns can prevent uniqueness from being enforced as expected.
• Certain special characters in key values can interfere with update or Upsert operations using key-based addressing.
• Alternate-key indexes are created asynchronously.
• A newly defined key should not be used for integration until its index has become active.
• Field-level security on key columns can prevent supported key-based operations.

These limitations must be treated as part of the integration contract.

Reference: Define alternate keys to reference rows.

4. The Asynchronous Key Index Lifecycle

Creating an alternate key is not the same as immediately having an active unique index.

Dataverse creates the supporting index through a background process.

The alternate key can move through states such as:

Pending
   |
   v
In Progress
   |
   +---- Active
   |
   +---- Failed

An integration must verify that the key is active before relying on it.

For this experiment, we will inspect the key metadata and record its status.

A failed index creation may indicate existing duplicate data or another configuration problem.

This introduces an important deployment dependency: a solution containing an alternate key should not automatically be considered ready for production synchronization merely because the solution import completed.

The key’s operational state must be verified.

5. Upsert Semantics

Upsert combines create and update behavior.

Conceptually:

Upsert
  |
  v
Find record using key
  |
  +---- Exists ----> Update
  |
  +---- Missing ---> Create

The Dataverse SDK exposes UpsertRequest.

var customer = new Entity(
    "pea_externalcustomer",
    "pea_externalcode",
    "CUST-10042");
customer["pea_name"] = "Contoso";
var response = (UpsertResponse)service.Execute(
    new UpsertRequest
    {
        Target = customer
    });

This example uses a single-column alternate key. Our laboratory will use a composite key.

The response includes RecordCreated, indicating whether the operation created a new record.

5.1 Important distinction: Upsert is not a read-then-write client pattern

A naive integration might perform:

Retrieve by external code
        |
        v
If found, Update
If missing, Create

This introduces a race condition.

Two clients may both observe that a record does not exist and attempt to create it.

Using the platform’s Upsert operation avoids implementing this particular client-side check-then-act sequence.

However, the integration must still handle unique-key conflicts, service errors, concurrent updates, and retries according to the actual operation semantics.

6. Application Architecture

APP-004 introduces a synchronization laboratory with three Dataverse tables.

External Source Simulator
          |
          v
C# Integration Client
          |
          v
Dataverse UpsertRequest
          |
          v
Alternate Key Resolution
          |
          +---- Existing Record -> Update
          |
          +---- Missing Record -> Create
          |
          v
Dataverse Persistence
          |
          +---- External Customers
          +---- Synchronization Attempts
          +---- Integration Runs
          |
          v
Model-Driven App
          |
          +---- Entry Dashboard
          +---- Customer Registry
          +---- Synchronization Diagnostics

The C# application simulates an external system.

The Model-Driven App provides visibility into the resulting records and integration attempts.

This separation allows the experiment to investigate SDK behavior without hiding it behind a user-interface workflow.

7. Dataverse Data Model

The application will extend the PEA.Dataverse solution.

7.1 External Customer

Logical name: pea_externalcustomer

Composite alternate key:

pea_sourcesystem + pea_externalcode

Proposed schema name:

pea_ExternalCustomer_SourceCode

The actual published key name must be confirmed through metadata.

Both key columns should be required by the integration contract, and the client must reject null or empty identifiers before calling Dataverse.

7.2 Synchronization Attempt

Logical name: pea_syncattempt

This table records individual Upsert attempts.

7.3 Integration Run

Logical name: pea_integrationrun

The run record provides a summary for the Model-Driven App dashboard.

8. Independent C# Project

The project will be:

PowerPlatformEngineeringAcademy/
└── Dataverse/
    └── APP004.AlternateKeyUpsertLab/
        ├── APP004.AlternateKeyUpsertLab.csproj
        └── Program.cs

Create the project:

dotnet new console --name APP004.AlternateKeyUpsertLab
cd APP004.AlternateKeyUpsertLab
dotnet add package Microsoft.PowerPlatform.Dataverse.Client

The program will execute a sequence of controlled Upsert operations.

Test sequence

1. Insert an external customer.
2. Repeat the same Upsert.
3. Change a non-key attribute.
4. Upsert the modified customer.
5. Retrieve the customer using its alternate key.
6. Inspect whether only one Dataverse customer record exists.
7. Record each attempt and its outcome.

The expected behavior is that the first operation creates the record and subsequent operations update the same record.

The experiment must verify this by inspecting the returned record identifiers and querying the persisted data.

9. Complete C# Implementation

The following Program.cs provides a self-contained integration experiment.

It uses a composite alternate key, performs repeated Upsert operations, verifies the resulting record identifier, and records individual synchronization attempts.

The code assumes the three proposed tables and their columns have already been created, the alternate-key index is Active, and the executing identity has the required Dataverse privileges.

<CodeBlock language=”csharp” editable>
using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;

const string CustomerTable = “pea_externalcustomer”;
const string AttemptTable = “pea_syncattempt”;
const string RunTable = “pea_integrationrun”;
const string KeyName = “pea_ExternalCustomer_SourceCode”;

string connectionString =
Environment.GetEnvironmentVariable(“DATAVERSE_CONNECTION”)
?? throw new InvalidOperationException(
“DATAVERSE_CONNECTION is missing.”);

using var client = new ServiceClient(connectionString);

if (!client.IsReady)
throw new InvalidOperationException(
$”Connection failed: {client.LastError}”);

var identity = (WhoAmIResponse)client.Execute(
new WhoAmIRequest());

Console.WriteLine($”Connected user: {identity.UserId}”);

// Verify the alternate key is active.
var metadataRequest = new RetrieveEntityKeyRequest
{
EntityLogicalName = CustomerTable,
LogicalName = KeyName
};

var metadataResponse = (RetrieveEntityKeyResponse)
client.Execute(metadataRequest);

EntityKeyMetadata keyMetadata = metadataResponse.EntityKeyMetadata;

Console.WriteLine($”Key status: {keyMetadata.EntityKeyIndexStatus}”);

if (keyMetadata.EntityKeyIndexStatus != EntityKeyIndexStatus.Active)
throw new InvalidOperationException(
“Alternate key index is not active.”);

string source = “ERP01”;
string externalCode = “CUST-10042”;

var startedOn = DateTime.UtcNow;
var run = new Entity(RunTable)
{
[“pea_name”] = $”APP004-{Guid.NewGuid()}”,
[“pea_startedon”] = startedOn,
[“pea_totalrequests”] = 0,
[“pea_createdcount”] = 0,
[“pea_updatedcount”] = 0,
[“pea_failedcount”] = 0
};

Guid runId = client.Create(run);

int total = 0;
int created = 0;
int updated = 0;
int failed = 0;

Guid? firstRecordId = null;

void ExecuteUpsert(string customerName, decimal creditLimit)
{
total++;
var stopwatch = Stopwatch.StartNew();

bool succeeded = false;
bool recordCreated = false;
Guid? recordId = null;
string message;
try
{
    var keys = new KeyAttributeCollection
    {
        { "pea_sourcesystem", source },
        { "pea_externalcode", externalCode }
    };
    var target = new Entity(CustomerTable, keys)
    {
        ["pea_name"] = customerName,
        ["pea_creditlimit"] = creditLimit,
        ["pea_lastsourceupdate"] = DateTime.UtcNow
    };
    var response = (UpsertResponse)client.Execute(
        new UpsertRequest { Target = target });
    recordCreated = response.RecordCreated;
    recordId = response.Target.Id;
    succeeded = true;
    if (recordCreated) created++;
    else updated++;
    message = recordCreated
        ? "Record created"
        : "Existing record updated";
    if (firstRecordId is null)
        firstRecordId = recordId;
    Console.WriteLine(
        $"{message}: {recordId}");
}
catch (Exception ex)
{
    failed++;
    message = $"{ex.GetType().Name}: {ex.Message}";
    Console.Error.WriteLine(message);
}
finally
{
    stopwatch.Stop();
}
var attempt = new Entity(AttemptTable)
{
    ["pea_name"] = $"ATTEMPT-{Guid.NewGuid():N}",
    ["pea_sourcesystem"] = source,
    ["pea_externalcode"] = externalCode,
    ["pea_operation"] = "Upsert",
    ["pea_recordcreated"] = recordCreated,
    ["pea_success"] = succeeded,
    ["pea_durationms"] = checked(
        (int)stopwatch.ElapsedMilliseconds),
    ["pea_message"] = message,
    ["pea_executedon"] = DateTime.UtcNow
};
try
{
    client.Create(attempt);
}
catch (Exception ex)
{
    Console.Error.WriteLine(
        $"Attempt logging failed: {ex.GetType().Name}");
}

}

// First operation: expected Create.
ExecuteUpsert(“Contoso”, 1000m);

// Same identity: expected Update.
ExecuteUpsert(“Contoso”, 1000m);

// Changed value: expected Update.
ExecuteUpsert(“Contoso Updated”, 1500m);

// Verify using the same composite alternate key.
var query = new QueryExpression(CustomerTable)
{
ColumnSet = new ColumnSet(
“pea_name”,
“pea_sourcesystem”,
“pea_externalcode”,
“pea_creditlimit”),
Criteria = new FilterExpression(LogicalOperator.And)
};

query.Criteria.AddCondition(
“pea_sourcesystem”, ConditionOperator.Equal, source);

query.Criteria.AddCondition(
“pea_externalcode”, ConditionOperator.Equal, externalCode);

EntityCollection matches = client.RetrieveMultiple(query);

Console.WriteLine($”Matching records: {matches.Entities.Count}”);

if (matches.Entities.Count != 1)
throw new InvalidOperationException(
“Expected exactly one customer record.”);

Entity customer = matches.Entities.Single();

Console.WriteLine($”Dataverse ID: {customer.Id}”);
Console.WriteLine(
$”Customer name: {customer.GetAttributeValue<string>(“pea_name”)}”);
Console.WriteLine(
$”Credit limit: {customer.GetAttributeValue<decimal>(“pea_creditlimit”)}”);

if (firstRecordId.HasValue && firstRecordId != customer.Id)
throw new InvalidOperationException(
“Upsert resolved an unexpected record identifier.”);

var runUpdate = new Entity(RunTable, runId)
{
[“pea_completedon”] = DateTime.UtcNow,
[“pea_totalrequests”] = total,
[“pea_createdcount”] = created,
[“pea_updatedcount”] = updated,
[“pea_failedcount”] = failed
};

client.Update(runUpdate);

Console.WriteLine(
$”Total={total}, Created={created}, Updated={updated}, Failed={failed}”);

Environment.ExitCode = failed == 0 ? 0 : 1;
</CodeBlock>

9.1 What this implementation demonstrates

The KeyAttributeCollection identifies the target record using the two external identifiers.

The SDK separates key values from ordinary attributes. This is important because Microsoft advises against including alternate-key columns in the data being saved when those same columns identify the record. <Cite ref=”turn800222search0″/>

The code also inspects the alternate-key index before using it, rather than assuming that a newly created key is operational.

The final query checks whether the selected external identity resolves to exactly one Dataverse record.

Implementation qualification: The sample is not a production-grade integration engine. It does not enforce source-version ordering, independently retry failed logging, guarantee an Integration Run record will be finalized after an unexpected exception, or perform concurrency stress testing. Those are explicit follow-up experiments.

10. Upsert Through the Dataverse Web API

The Web API exposes equivalent record-addressing capabilities through HTTP.

For a composite alternate key:

PATCH https://<organization>.crm.dynamics.com/api/data/v9.2/<entity-set>(pea_sourcesystem='ERP01',pea_externalcode='CUST-10042')
Content-Type: application/json
Accept: application/json
{
  "pea_name": "Contoso",
  "pea_creditlimit": 1500.00
}

The actual entity set name must be discovered from Dataverse metadata, and key values must be correctly encoded for the URI.

A normal PATCH targeting a record by alternate key can create or update it.

The API also supports conditional headers.

Microsoft documents that ordinary Web API Upsert responses may return 204 No Content for both creation and update. The Prefer: return=representation header can distinguish the outcomes through 201 Created and 200 OK, but adds retrieval work. <Cite refs={[“turn800222search0″,”turn800222search11”]}/>

Why conditional requests matter

Suppose a customer record was deliberately deleted.

An ordinary Upsert may recreate it during the next synchronization.

That might be correct for a source-of-truth synchronization service. It might also violate the business requirement that deleted records must remain deleted.

Using If-Match: * prevents unintended creation.

Similarly, If-None-Match: * supports a create-only operation when overwriting an existing record would be unacceptable.

These conditions should be selected from the business contract, not simply from developer convenience.

11. Idempotency: What Upsert Does and Does Not Guarantee

An operation is idempotent when repeating it has the same intended effect on system state as executing it once.

Consider this synchronization payload:

{
  "externalCode": "CUST-10042",
  "creditLimit": 1500.00
}

If repeated Upserts set the same customer attributes to the same values, the resulting business values can remain stable.

However, our C# sample also updates pea_lastsourceupdate using the current execution time.

Consequently, repeated requests are not strictly state-idempotent: each retry changes the timestamp.

This is a deliberate teaching point.

Upsert can prevent duplicate records under a stable alternate key, but the operation’s complete idempotency depends on the payload and any associated processing.

Additional concerns include plug-ins, flows, auditing, and external side effects that may run when an update is processed.

11.1 The ambiguous timeout problem

Imagine the following sequence:

Integration Client             Dataverse
       |                           |
       |------ Upsert ------------>|
       |                           |
       |                    Record committed
       |                           |
       |<--- Response lost --------|
       |
       | Timeout: outcome unknown
       |
       |------ Retry ------------->|

The client cannot infer from a timeout whether Dataverse committed the original request.

A retry using the same stable alternate key can avoid creating a second customer record, provided the first operation committed and the key remains unchanged.

It does not guarantee that associated business logic runs only once.

For side-effecting integrations, additional deduplication or an idempotency-key ledger may be necessary.

12. Concurrency and Lost Updates

Consider two integration workers updating the same customer.

Worker A receives a source event with a credit limit of 1,500.

Worker B receives an older event with a credit limit of 1,000.

If Worker B executes last, the final value may become 1,000, even though Worker A processed the newer business state.

Alternate keys prevent identity duplication. They do not automatically establish source-event ordering.

Possible solutions include:

• Source-generated monotonically increasing version numbers.
• Conditional updates using Dataverse row versions or Web API ETags.
• Serialized processing by business key.
• Server-side validation of source versions.
• A synchronization ledger tracking processed events.

The correct strategy depends on the consistency contract.

Optimistic concurrency will be investigated in detail in APP-009.

Reference: Perform conditional operations using the Web API.

13. Model-Driven App: PEA Alternate Key & Upsert Lab

The application must provide a functional entry dashboard and usable operational pages.

13.1 Entry dashboard

The proposed landing dashboard displays:

It should also display recent synchronization attempts and integration runs.

These metrics describe the records captured by the laboratory. They are not guaranteed to represent every operation performed in the environment if logging fails.

13.2 Forms and views

The External Customer form displays source system, external code, customer name, credit limit, and source update timestamp.

The Synchronization Attempt form displays the submitted identity, operation, creation/update outcome, duration, and diagnostic result.

The Integration Run form displays the execution window and aggregate counts.

Views should allow filtering by source system, external code, success status, and execution date.

13.3 Manual inspection

The engineer should be able to open a customer record, inspect its alternate-key values, and compare the record against multiple logged Upsert attempts.

This provides direct evidence that repeated requests addressed the same record.

The Model-Driven App does not replace the C# synchronization client. It is the operational interface for observing and inspecting its behavior.

14. Failure Injection

The following tests are required before the application can be considered validated.

For negative tests, the application must verify the specific error or condition. An unrelated authentication or connectivity failure must not count as a successful validation test.

15. Performance and ALM

Alternate keys are backed by indexes and therefore affect both schema deployment and data operations.

A composite key should be selected based on stable identity requirements, not merely convenience.

The integration should avoid unnecessarily wide text keys and should account for the documented key-size limits.

For ALM, the solution must contain the relevant table, columns, and alternate-key definition.

After deployment, the key index must reach its active state before synchronization begins.

For performance testing, compare controlled workloads such as repeated Upsert, lookup by alternate key, and explicit retrieve-then-update. Measure request latency and failures without claiming universal performance differences from small samples.

16. Official Microsoft Learn References

1. Define alternate keys to reference rows — Key definitions, restrictions, index lifecycle, and integration considerations.
2. Use Upsert to create or update a record — SDK and Web API Upsert behavior.
3. Use an alternate key to reference a record — Entity.KeyAttributes and alternate-key addressing.
4. Define alternate keys using Power Apps — Maker experience and key constraints.
5. Update and delete table rows using the Web API — HTTP update and Upsert operations.
6. Perform conditional operations using the Web API — ETags, If-Match, and If-None-Match.

Conclusion

Alternate keys and Upsert are foundational capabilities for integrating Dataverse with external systems.

They allow integrations to identify records through business keys, avoid maintaining unnecessary GUID mappings, and perform create-or-update operations through a single platform request.

But reliable integration requires more than correct record identification.

Engineers must account for asynchronous key activation, invalid key values, ambiguous network failures, retries, concurrent writers, stale source events, and side effects.

The most important architectural distinction established by APP-004 is:

Alternate keys provide stable record identity. Upsert provides create-or-update semantics. Neither automatically guarantees exactly-once processing or conflict-free synchronization.

The proposed laboratory makes those boundaries observable through a C# integration client, persistent synchronization diagnostics, and a functional Model-Driven App.

Practical validation will determine which behaviors are confirmed in the target environment and which require refinement.

Power Platform Engineering Academy — Five Years in Five Days

Article 004/100 — Alternate Key & Upsert Lab

Stable identity. Repeatable operations. Explicit consistency contracts. Evidence before claims.