Faster Web API Metadata Access

I was recently updating a Javascript web resource that needs to use the Dataverse Web API to get some table metadata. This was taking around 5 seconds to get all the information it needed, but with a few changes I got that down to about 0.2 seconds.

Requirements

The script needed to access metadata for around 10 tables. For each table it needs details of all the columns, including:

• Choice columns: list of available options
• Lookup columns: list of possible target tables
• String columns: details of string format

Existing solution

The script was using the approach outlined in the docs to use the EntityDefinitions entity to get the basic table and column details using a request like:

GET [Organization URI/api/data/v9.0/EntityDefinitions
?$select=LogicalName,DisplayName
&$filter=Microsoft.Dynamics.CRM.In(PropertyName='LogicalName',PropertyValues=['account','contact','lead','...']))
&$expand=Attributes

Note that I’m using an IN condition here rather than a long chain of LogicalName eq 'account' OR LogicalName eq 'contact' OR .... This is because the list of filters is limited to 10, so once you get past 10 items in the OR list you’ll get an error.

The attributes that are returned by this are supposed to only include the properties that are common across all attribute types, but I’ve found it also includes the targets for lookup columns and the format for string columns. However, it doesn’t return the list of options for Choice columns, so I need to make additional requests (one per table) to get those details:

GET [Organization URI/api/data/v9.0/EntityDefinitions(LogicalName='account')/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata
?$select=LogicalName,OptionSet

In fact there are other column types that also define a list of options, so I need to make a total of 5 extra requests per table type, one for each of the column types:

• PicklistAttributeMetadata
• MultiSelectPicklistAttributeMetadata
• EntityNameAttributeMetadata
• StateAttributeMetadata
• StatusAttributeMetadata

So in total I’m making 1 initial request for the basic details of all the tables (about 1.5 seconds), then 5 requests per table to get the optionsets (0.1 seconds each). That’s a total of 51 requests for 10 tables, leading to some pretty poor performance. I wanted to see if I could do better.

Step 1: Reduce the information retrieved

You might have noticed in the first query I was using $expand=Attributes without specifying which properties of those attributes I wanted, so I ended up with everything. That was done for a reason: although the docs say that you’ll only get properties on the base AttributeMetadata type that are common across all attributes, I found this actually included the Targets and FormatName properties of the lookup and string column types as well. However, if I try to limit it using

GET [Organization URI/api/data/v9.0/EntityDefinitions
?$select=LogicalName,DisplayName
&$filter=Microsoft.Dynamics.CRM.In(PropertyName='LogicalName',PropertyValues=['account','contact','lead','...']))
&$expand=Attributes($select=LogicalName,DisplayName,Targets,FormatName)

This now breaks as the base AttributeMetadata type doesn’t define the Targets and FormatName properties, even though they’re returned if you don’t specify a list of properties. I thought I’d give it a go however, and switch to:

GET [Organization URI/api/data/v9.0/EntityDefinitions
?$select=LogicalName,DisplayName
&$filter=Microsoft.Dynamics.CRM.In(PropertyName='LogicalName',PropertyValues=['account','contact','lead','...']))
&$expand=Attributes($select=LogicalName,DisplayName)

so I only get the absolute basic information I need for each attribute, and add two more requests per table to get the Targets and FormatName properties for lookup and string columns.

Overall I’m now getting less data but I’m also making more requests to get it. In total I’m now making 71 requests and taking slightly longer, so not a great improvement.

Step 2: Single request

For my next trick I’m going to make 70 of those requests disappear!

In C# we can use the RetrieveMetadataChangesRequest to efficiently get a filtered set of metadata, and since v9 we can also use this in Web API too with the RetrieveMetadataChanges function.

To use this function we first need to build up a query for the information we want. This isn’t something you want to build directly into a URL, instead I’m going to create it in Typescript:

const query = {
  Criteria: {
    Conditions: [
      { PropertyName: "LogicalName", ConditionOperator: "In", Value: { Type: "System.String[]", Value: "['account', 'contact', 'lead', '...']" } }
    ]
  },
  Properties: {
    AllProperties: false,
    PropertyNames: ["LogicalName", "DisplayName", "Attributes"]
  },
  AttributeQuery: {
    Properties: {
      AllProperties: false,
      PropertyNames: ["LogicalName", "DisplayName", "OptionSet", "Targets", "FormatName"]
    }
  }
};
const url = `[Organization URI]/api/data/v9.0/RetrieveMetadataChanges(Query=@p0)?@p0=${JSON.stringify(query)}`;

Unlike the EntityDefinitions entity, I can ask for the OptionSet, Targets and FormatName properties of all the columns in this single query.

One slight complexity of building this query is the Value property for conditions. Because this can be many different types depending on the property and operator the condition applies to, you have to specify the type as well as the value.

The Type property needs to be the corresponding .NET type that you’d use from an SDK application (System.String[] in this example for a list of strings).

The details of the Value format are a bit of a mystery to me. For simple values like a string, number etc. you can use a standard JSON string. For arrays a few different formats seem to work:

• "['account', 'contact']"
• "[account, contact]"
• "account, contact"

⚠ Warning!
I’m not sure which of these would be considered “correct” as the documentation isn’t clear. It’s possible that some work by accident and so could stop working in any future update.

Now I’ve got that massive URL built up I can use it to retrieve all the metadata in one go. This brings me straight down to 1 request and about half the time – 2.5 seconds.

Step 3: caching

As metadata doesn’t change often, it make sense to cache it for future requests. As the name implies, RetrieveMetadataChanges will give you the changes to the metadata since the last time you asked for it. Each request gives you not only the metadata you’ve asked for but also the current version number. If you supply that version number to the next request it will give you only the changed metadata since that version.

Using the window.localStorage object we can save the response from the first request, and when we load the page again we can access this value, check if there are any changes and update the cache with the new value.

When there aren’t any changes, which will happen most of the time, each request only takes about 0.1 – 0.2 seconds!

The final problem we’ve got to solve is the size of the cached metadata. Local storage in the browser is typically limited to only 5MB per domain, and Dynamics is already using some of that. To keep our use of space down I’ve used the lz-string library to compress the JSON version of the metadata.

Final code

After those changes my final code now looks like:

async getMetadata(): Promise<EntityMetadata[]> {
  const query = {
    Criteria: {
      Conditions: [
        { PropertyName: "LogicalName", ConditionOperator: "In", Value: { Type: "System.String[]", Value: "['account', 'contact', 'lead', '...']" } }
      ]
    },
    Properties: {
      AllProperties: false,
      PropertyNames: ["LogicalName", "DisplayName", "Attributes"]
    },
    AttributeQuery: {
      Properties: {
        AllProperties: false,
        PropertyNames: ["LogicalName", "DisplayName", "OptionSet", "Targets", "FormatName"]
      }
    }
  };

  // If we've got a version in the cache, re-validate it
  const cacheKey = "mcd.metadatacache";
  const cachedString = window.localStorage.get(cacheKey);
  if (cachedString) {
    const parsedCache = JSON.parse(LZString.decompress(cachedString));
    const reCheckUrl = `[Organization URI]/api/data/v9.0/RetrieveMetadataChanges(Query=@p0,ClientVersionStamp=@p1)?@p0=${JSON.stringify(query)}&@p1=${parsedCache.ServerVersionStamp}`;
    const response = await fetch(reCheckUrl);
    if (response.ok) {
      const metadataChanges = await response.json();
      if (metadataChanges.EntityMetadata.length == 0 && metadataChanges.DeletedMetadata == null) {
        // No changes since the cached version
        return parsedCache.EntityMetadata;
      }

      // If there were some changes we could merge them in to the cached version, but I'm taking a simple
      // approach of throwing away the cached version and getting a whole fresh copy for simplicity.
    }
  }

  // We don't have a cached version available, or it needs updating
  const url = `[Organization URI]/api/data/v9.0/RetrieveMetadataChanges(Query=@p0)?@p0=${JSON.stringify(query)}`;
  const response = await fetch(url);
  const metadata = await response.json();

  // Add the new version to the cache
  window.localStorage.set(cacheKey, LZString.compress(JSON.stringify(metadata)));

  return metadata.EntityMetadata;
}