Out of the box with Microsoft Dataverse, a single table row may be referenced using pluralized-table-name(primary-key) syntax, like:
GET {{webApiUrl}}stores(78c91f30-0e86-4716-8718-176c14174cab)
(include the "always include" headers)
However, there are times where you know that rows in a table may also be uniquely identified using another column’s value (or maybe even a unique set of values across several columns). For example, imagine a table describing your company’s stores. In the technical domain, Dataverse identifies each row using an auto-assigned GUID primary key—but to the business, each store is identified by a numerical store number stored in a StoreId column, like StoreId = 1 or StoreId = 2.
You can formally recognize the presence of an alternate unique identifier by declaring the relevant column (or columns) as an alternate key.
Defining an alternate key brings with it several advantages, including:
- It’s uniqueness will be enforced. The system will not allow multiple rows to have the same key value (so, in our example, if StoreId is declared as an alternate key, two records with
StoreId = 1won’t be allowed). - The ability to reference rows using the alternate key (more on this below).
- Optimized lookup performance.
Fetching Data with an Alternate Key
To address a record using a single-column alternate key: Inside the parenthesis after the pluralized table name, put the key’s column name (not the key’s name itself, but the name of the column referenced by the key), an equals sign, then the value you’re looking for in that column (surrounded by quotes, when appropriate), like this:
GET {{webApiUrl}}stores(my_storeid=25)
(include the "always include" headers)
Above, the GET request fetches store id 25’s details. Nice!
In the case of a multi-column key, a comma-separated column name + value pairs list goes between the parenthesis (with values quoted, when appropriate, of course!). The order of the column names doesn’t matter—it doesn’t have to match the order used when defining the key.
Below, store 25 in the US region is fetched using an alternate key defined on columns region and store id.
GET {{webApiUrl}}stores(my_region='US',my_storeid=25)
(include the "always include" headers)
As an aside, did you notice that there’s a difference in uniqueness enforcement between the two keys in this example? The first ensures that, at most, one store 25 exists; the second allows, at most, one store 25 per region exists (but there could be a 25 in the US and another in the UK, etc.). Which is appropriate depends on the particulars of your situation.
Anti-Pattern
Using an entity collection request that’s equality filtered on a column (or columns) to return what should always be, at most, a single row, regardless of the particular value(s) searched for likely signals the presence of an alternate key.
GET {{webApiUrl}}stores?$top=1&$filter=my_storeid eq 25
(include the "always include" headers)
If, indeed, an alternate key is present—whether it’s an implicit alternate key or one that’s been explicitly defined—then arguably it is an anti-pattern to use a filtered collection request like the above.
If the alternate key has been declared, instead of using the above style request, consider addressing the row of interest directly using the key (e.g. GET {{webApiUrl}}stores(my_store=25)).
If the key hasn’t been formally declared (that is, it’s an implicit alternate key), consider officially defining the alternate key. This will allow you to reap the benefits of the system “knowing” about it, including the ability to address rows directly using it.
Points of Awareness
Null Values
Dataverse alternate keys only apply to non-null values. In the case of our StoreId example, the alternate key’s presence won’t disallow multiple StoreId=null rows, nor can it be used to access these rows (so GET {{webApiUrl}}stores(my_storeid=null) won’t work).
For multi-column alternate keys, all of the key’s columns must contain non-null values in order for the key to apply. The region + store id alternate key will apply to rows where both region and store id are set but not to rows with just a region or just a store id.
You could think of an alternate key’s duplicate prevention behavior as working like a SQL index defined along the lines of the following : CREATE UNIQUE INDEX UX_SomeName (my_region, my_storeid) WHERE my_region IS NOT NULL AND my_storeid IS NOT NULL.
Special Characters
/, <, >, *, %, &, :, \\ and ? can’t be used in alternate key URL references. This limitation means that GET {{webApiUrl}}stores(my_region='/NorthAmerica') won’t work because of the forward slash in the referenced key column’s value. The alternate key itself works fine with these special characters (i.e. its duplicate prevention behavior still works, as expected), just these characters can’t successfully be used when referencing a row by the key in a URL.
Note
Don’t forget to include the “always include” headers with every request sent to the Web API.