Using Alternate Keys in Dynamics 365 – Dataverse

Today I want to talk about a feature that I think is underused on most integration projects: alternate keys. If you have ever built a sync between Dataverse and an external system, you have almost certainly run into the problem I am going to describe. And once I show you how alternate keys solve it, you will wonder how you managed without them.

The Problem

Here is the scenario. You are building an integration that pushes product data from an ERP system into Dataverse on a nightly schedule. The ERP has its own product codes. Dataverse has GUIDs. They mean nothing to each other.

Every time a product record comes in from the ERP, your integration code has to do something like this:

Query Dataverse to find the record by product code
Check if anything was returned
If yes, grab the GUID from the result and use it to update
If no, create a new record

You end up writing boilerplate query logic for every table you integrate. You double the number of API calls per record. You add error surface area. And if the query returns more than one result because of a data quality issue, you have a problem.

Alternate keys eliminate most of that.

What is an Alternate Key?

An alternate key is a column, or a combination of columns, that you declare as a unique identifier on a Dataverse table. Dataverse creates a database index behind it to enforce uniqueness and speed up lookups by that value. It sits alongside the system-generated GUID primary key rather than replacing it.

Once defined, you can use the alternate key value directly in SDK operations without ever looking up or knowing the GUID. Dataverse resolves the alternate key to the primary key internally.

The closest analogy in relational databases is a unique index on a non-primary column. You still have the surrogate key for internal joins, but you expose a natural business key to external systems.

Supported Column Types

Not every column type can be part of an alternate key. The supported types are:

Single line of text
Whole number
Decimal number
Date and time
Lookup
Option set (choice)

A few constraints apply. Field-level security must be disabled on any column included in an alternate key. A table can have at most ten alternate key definitions. Each key definition can include at most sixteen columns, and the total key size must stay under 900 bytes, which is the SQL index constraint.

Also worth noting: if any column value in your key contains the characters /, <, >, *, %, &, :, \, ?, or +, retrieve, update, and upsert operations using that key will fail. You can still use the key to enforce uniqueness, but you cannot reference records by it in code if those characters appear in the data.

Defining an Alternate Key

There are two ways to create an alternate key.

The first is through the Power Apps maker portal. Go to the table you want to configure, open the Keys section, and add a new key by selecting the columns that should form it. No code needed. This is the right choice for most situations.

The second is through the SDK using CreateEntityKeyRequest. This is useful when you are building a setup script or a solution component installer that needs to provision the key programmatically:

			
public static void CreateProductCodeKey(IOrganizationService service)
{
    var keyMetadata = new EntityKeyMetadata
    {
        DisplayName = new Label("Product Code Key", 1033),
        SchemaName = "contoso_ProductCodeKey",
        KeyAttributes = new string[] { "contoso_productcode" }
    };
    var request = new CreateEntityKeyRequest
    {
        EntityName = "contoso_product",
        EntityKey = keyMetadata
    };
    service.Execute(request);
}

		

One thing to be aware of: index creation runs as an asynchronous background job. The key is not immediately usable after the request succeeds. You can check the EntityKeyIndexStatus property on the key metadata to see where it is in the process. The possible values are Pending, In Progress, Active, and Failed. The key is only usable once the status is Active. On tables with a large number of existing records, this can take several minutes.

If the index creation fails, you can inspect the error, fix the underlying data issue, and reactivate the key using ReactivateEntityKeyRequest.

Using an Alternate Key to Reference a Record in Code

Once a key is Active, you can use it in SDK operations through KeyAttributeCollection. The key values go into KeyAttributes on the entity, not into the regular Attributes collection. This distinction matters. KeyAttributes is how you tell the SDK which record you are targeting. Attributes is the data you want to write.

Both the Entity and EntityReference classes support alternate key constructors. For a single-column key, you can use the shorthand constructor that takes the key column name and value directly. For multi-column keys, you use KeyAttributeCollection.

Here is what the constructors look like:

			
// Single-column alternate key: shorthand constructor on Entity
var product = new Entity("contoso_product", "contoso_productcode", "SKU-1042");
// Multi-column alternate key: KeyAttributeCollection on Entity
var keys = new KeyAttributeCollection
{
    { "contoso_productcode", "SKU-1042" },
    { "contoso_region", "EU" }
};
var product = new Entity("contoso_product", keys);
// Single-column alternate key on EntityReference (for lookup fields and delete)
var productRef = new EntityReference("contoso_product", "contoso_productcode", "SKU-1042");
// Multi-column alternate key on EntityReference
var productRef = new EntityReference("contoso_product", keys);

		

Common Operations with Alternate Keys

Retrieve

To retrieve a record by alternate key, pass an EntityReference built with the key to a RetrieveRequest. Dataverse resolves the key to the primary key before executing the query:

			
public static Entity RetrieveProductBySku(IOrganizationService service, string sku)
{
    var target = new EntityReference("contoso_product", "contoso_productcode", sku);
    var request = new RetrieveRequest
    {
        Target = target,
        ColumnSet = new ColumnSet("contoso_productname", "contoso_unitprice", "contoso_stocklevel")
    };
    var response = (RetrieveResponse)service.Execute(request);
    return response.Entity;
}

		

Update

To update by alternate key, construct the Entity using the key values. The SDK resolves the key to the underlying GUID before writing the changes. You only provide the fields you want to change in Attributes:

			
public static void UpdateProductPrice(IOrganizationService service, string sku, decimal newPrice)
{
    // Target the record by alternate key
    var product = new Entity("contoso_product", "contoso_productcode", sku);
    // Set the fields to update in Attributes, not in KeyAttributes
    product["contoso_unitprice"] = new Money(newPrice);
    product["contoso_lastupdated"] = DateTime.UtcNow;
    service.Update(product);
}

		

Delete

For delete, use EntityReference with the alternate key and wrap it in a DeleteRequest:

			
public static void DeleteProductBySku(IOrganizationService service, string sku)
{
    var target = new EntityReference("contoso_product", "contoso_productcode", sku);
    var request = new DeleteRequest { Target = target };
    service.Execute(request);
}

		

Setting a Lookup Field by Alternate Key

This is a use case that surprises many developers. You can set a lookup column on a record by providing an EntityReference built with an alternate key. Dataverse resolves the key to the GUID and stores the primary key in the database. The calling code never needs to know the GUID:

			
public static void AssignCategoryToProduct(
    IOrganizationService service,
    string productSku,
    string categoryCode)
{
    // Reference the category by its alternate key
    var categoryRef = new EntityReference("contoso_category", "contoso_categorycode", categoryCode);
    // Update the product's category lookup without knowing either GUID
    var product = new Entity("contoso_product", "contoso_productcode", productSku);
    product["contoso_categoryid"] = categoryRef;
    service.Update(product);
}

		

This pattern is particularly useful in integration scenarios where you have natural keys on both sides of a relationship and you want to wire them up without doing any pre-queries.

Upsert: Where Alternate Keys Really Shine

The combination of alternate keys and UpsertRequest is the most powerful pattern for integration sync jobs. Upsert creates a record if it does not exist, or updates it if it does. With an alternate key, you do not need to query first to decide which branch to take. You just send the data and let Dataverse figure it out.

Here is what a clean upsert looks like for the ERP product sync scenario:

			
public static bool SyncProductFromErp(
    IOrganizationService service,
    string sku,
    string productName,
    decimal unitPrice,
    int stockLevel)
{
    // Use the alternate key to target the record
    // Dataverse will create it if the SKU does not exist, or update it if it does
    var product = new Entity("contoso_product", "contoso_productcode", sku);
    product["contoso_productname"] = productName;
    product["contoso_unitprice"] = new Money(unitPrice);
    product["contoso_stocklevel"] = stockLevel;
    product["contoso_lastsynctime"] = DateTime.UtcNow;
    var request = new UpsertRequest { Target = product };
    var response = (UpsertResponse)service.Execute(request);
    // RecordCreated is true if a new record was created, false if an existing one was updated
    return response.RecordCreated;
}

		

Compare this to the old approach. Before alternate keys, syncing a product required a RetrieveMultiple to find the record by SKU, a conditional check on the result, and then either a Create or an Update. That is at minimum two API calls per record in the update path. With an alternate key and upsert, it is always one call.

For a nightly sync of 10,000 products, that difference matters.

SDK Error Conditions to Know

A few error conditions are worth understanding before you go and write code against alternate keys.

If you provide both Entity.Id (the GUID) and a KeyAttributeCollection, the GUID wins. The key collection is ignored. This is easy to miss if you are refactoring existing code that already had a GUID and you are adding an alternate key lookup on top of it.

If you include a column in the KeyAttributeCollection that is not part of any declared alternate key on that table, the SDK throws an error. The column has to be explicitly defined as part of a key definition, not just unique in practice.

If the set of columns you provide in the KeyAttributeCollection does not match any defined key definition, the SDK throws an error. The combination has to match exactly.

Finally, as mentioned earlier, if the value of a key column contains any of the special characters listed in the constraints section, retrieve, update, and upsert by alternate key will fail. This is a common issue when integrating with systems that use URL-style identifiers or path-formatted codes.

Real-World Use Cases

ERP product catalog sync. This is the scenario I described at the start. The ERP owns the SKU. Dataverse stores a copy for Power Apps forms and reporting. The nightly sync job uses SKU as the alternate key, fires one upsert per product, and never queries for GUIDs. The same pattern works for customers, vendors, price lists, and any other master data the ERP owns.

Order status writeback. An external order management system writes order status updates back into Dataverse. The external system knows the order number but not the Dataverse GUID. An alternate key on the order number column makes the writeback a single API call per order, with no lookup step.

Two-way sync with a third-party CRM. A customer had both Dynamics 365 and a marketing platform. Each system had its own contact ID. By storing the marketing platform’s contact ID in a custom column with an alternate key, both sync directions could target records without ever exchanging internal GUIDs between the two systems.

Setting relationships in bulk without pre-queries. When migrating or importing data that includes lookup relationships, alternate keys on both sides of a relationship let you set lookups by natural keys directly during the import. No pre-loading of GUID maps required.

When to Avoid Alternate Keys

Alternate keys are not the right answer in every situation.

Do not define an alternate key on a virtual table. The platform cannot enforce uniqueness when the data lives in an external system, and the feature is not supported.

Do not define an alternate key on a column that regularly contains the special characters listed earlier. Even if the key is valid and active, any record with those characters becomes unreachable through the alternate key.

Be careful about defining alternate keys on very large tables in production. The index creation runs in the background, but the index itself adds overhead to every insert and update on that column from that point forward. On a table that receives thousands of writes per minute, this is worth thinking through.

Finally, do not define an alternate key just because a column happens to hold unique values. Only create an alternate key if you genuinely need to reference records by that column in code or in an integration. Unnecessary keys add maintenance cost and index overhead for no benefit.

Wrapping Up

Alternate keys are one of those features that once you start using them, you reach for them automatically on every integration project. The combination with upsert in particular eliminates a whole class of query-then-write patterns that slow down sync jobs and add unnecessary API calls.

The things to remember are: define the key through the portal unless you need it in a provisioning script, wait for the index to reach Active status before using it, keep key values free of special characters, and always put key values in KeyAttributes rather than Attributes.