Dataverse Web API Tip #12: Lookup Property Annotations

In Dataverse Web API responses, you’ll sometimes see properties named along the lines of _{navigation property name}_value (like _owerid_value). These are lookup properties: system-computed, read-only values that appear on the many side of a many-to-one relationship and hold the primary key of the related record.

Often, you won’t need to touch these lookups, thanks to navigation properties—which are the preferred way to work with relationships. However, the annotations on lookup properties can come in handy even in cases where the lookup values themselves aren’t interesting—particularly when a relationship can point to more than one table (i.e. can point to more than one type of entity, like column types customer and owner).

Suppose your orders table has a SoldTo column of type customer. This column type can hold a reference to either an account entity or a contact entity, allowing an order to be sold to either a company (account) or an individual (contact). Logically, SoldTo is one relationship; but technically, it has two navigation properties, one for the connection to accounts and the other to contacts. For a given order, how do you know what type of entity this relationship points to and so which of the two navigation properties to use?

You may find two annotation types helpful:

• Microsoft.Dynamics.CRM.lookuplogicalname indicates the type of entity that the lookup currently references.
• Microsoft.Dynamics.CRM.associatednavigationproperty holds the names of its associated single-valued navigation property.

Below, both are requested using a Prefer: odata.include-annotations="{comma-separated list of desired annotations}" header.

GET {{webApiUrl}}my_orders
Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname" 
(include the "always include" headers)

In the response, lookuplogicalname‘s value tells us that the first order’s customer is a contact and that the second order’s is an account. The values in associatednavigationproperty guide us to the appropriate single-valued navigation property to use to access each.

{
  "@odata.context": "{{webApiUrl}}$metadata#my_orders",
  "value": [
    {
      "@odata.etag": "W/\"1614074\"",
      "_my_customer_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "my_customer_contact",
      "_my_customer_value@Microsoft.Dynamics.CRM.lookuplogicalname": "contact",
      "_my_customer_value": "00000000-0000-0000-0000-000000000002",
      …
    },
    {
      "@odata.etag": "W/\"1614078\"",
      "_my_customer_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "my_customer_account",
      "_my_customer_value@Microsoft.Dynamics.CRM.lookuplogicalname": "account",
      "_my_customer_value": "d36faadb-47a8-eb11-b1ac-000d3a32b6cd",
      …
    }
  ]
}

Not the Only Way

Using these annotations isn’t the only way to figure out the related entity type or navigation property to use. Another option is to $expand both properties, then check which one is non-null. Sometimes, $expanding like this works great (and when it does, you may prefer to use it). However, the Web API only allows 10 $expands in a GET request. In contrast, fetching lookup annotations has no maximum count limit and so can be used without worrying about maxing out on $expands.

Also, in those special cases (like data integration/synchronization) where you want (or need) to directly use a lookup property’s value, these annotations can be essential—because without them you may be unable to fully interpret a multi-type lookup’s value.

Reference

Retrieve data about lookup properties (in Query data using the Web API)

Note

Don’t forget to include the “always include” headers with every request sent to the Web API.