Muokkaa

Jaa

Query schema definitions

  • Artikkeli

Applications built using Dataverse need to be able to adapt to changes to schema definitions. New tables, columns, relationships, and labels can be added or changed via configuration or by importing a solution. Because applications need to be able to respond to these changes, they frequently depend on retrieving schema definitions when they start. However, the total amount of data describing the schema of a Dataverse organization can be very large. You need to be able to know how to get just the data you need.

The RetrieveMetadataChanges message provides two capabilities:

  1. Query: Compose a single query to retrieve just the schema data you need. This article focuses on composing queries.
  2. Cache Management: If you cache the schema definition data with your app, you can use RetrieveMetadataChanges to efficiently retrieve only the changes since your last query. Use the information about these changes to add or remove items in your cache. Caching may result in a significant improvement time for your application to start. Cache management is covered in Cache Schema data.

Evaluate other options to retrieve schema definitions

When composing a query to retrieve schema definitions, RetrieveMetadataChanges message provides an advantage to define a single request that can span multiple table definitions and return details for derived types and manage a cache over time.

The following table summarizes other ways you can retrieve schema definitions, but none of them provide capabilities to manage a cache over time.

Message Description and limitations
RetrieveAllEntities Retrieves data for all tables, including all columns, privileges, and relationships if you wish.
See: RetrieveAllEntitiesRequest and RetrieveAllEntitiesResponse classes.

Limitations: While you can use the EntityFilters parameter to exclude some parts, it's a very expensive operation.
RetrieveEntity You can retrieve definition of any single table including all columns, privileges, and relationships if you wish.
See: Retrieve and update a table

Limitations: While you can use the EntityFilters parameter to exclude some data, you can't select which specific properties you want, it's still an expensive operation.
RetrieveAttribute You can retrieve the complete definition of any single attribute.
See: Retrieve a column

Limitations: You can't select which specific properties you want.
RetrieveRelationship You can retrieve the complete definition of any single relationship.
See: Retrieve table relationships

Limitations: You can't select which specific properties you want.
RetrieveAllOptionSets You can retrieve information about all the global choices defined in the organization.
See: Insert, update, delete, and order global choices

Limitations: Choices that are only defined locally within a column aren't included.
RetrieveEntityKey You can retrieve the definition for any alternate keys for a specific table.
See:Retrieve and delete alternate keys

Basic RetrieveMetadataChanges example

For a simple example of what you can do with RetrieveMetadataChanges, compare what you can do with Web API and the EntityDefinitions entity set.

Using Web API you can compose a query like this:

GET [Organization URI]/api/data/v9.2/EntityDefinitions?$select=SchemaName&$filter=LogicalName eq 'account' or LogicalName eq 'contact'&$expand=Attributes($select=LogicalName;$filter=IsValidForCreate eq true)

This query returns data from both the account and contact table definitions, and expand all the column definitions where IsValidForCreate is true.

The following examples show how to compose the same query using RetrieveMetadataChanges.

/// <summary>
/// Get the SchemaName for the account and contact tables together with
/// the LogicalName of any attributes which are valid for create
/// </summary>
/// <param name="service"></param>
static void SimpleRetrieveMetadataChangesExample(IOrganizationService service) {

    var query = new EntityQueryExpression
    {
        Properties = new MetadataPropertiesExpression("SchemaName", "Attributes"),
        Criteria = new MetadataFilterExpression(filterOperator: LogicalOperator.Or)
        {
            Conditions = {
                {
                    new MetadataConditionExpression(
                        propertyName:"LogicalName",
                        conditionOperator: MetadataConditionOperator.Equals,
                        value:"account")
                },
                {
                    new MetadataConditionExpression(
                        propertyName:"LogicalName",
                        conditionOperator: MetadataConditionOperator.Equals,
                        value:"contact")
                }
            }, 
        },
        AttributeQuery = new AttributeQueryExpression
        {
            Properties = new MetadataPropertiesExpression("LogicalName"),
            Criteria = new MetadataFilterExpression(filterOperator: LogicalOperator.And)
            {
                Conditions = {
                    {
                        new MetadataConditionExpression(
                        propertyName:"IsValidForCreate",
                        conditionOperator: MetadataConditionOperator.Equals,
                        value:true)
                    }
                }
            }            
        },
        LabelQuery = new LabelQueryExpression { 
             FilterLanguages = {
                { 1033 }
            } 
        }
        
    };

    var request = new RetrieveMetadataChangesRequest
    {
        Query = query
    };

    var response = (RetrieveMetadataChangesResponse)service.Execute(request);

    response.EntityMetadata.ToList().ForEach(em => {

        Console.WriteLine($"Entity SchemaName:{em.SchemaName}");
        em.Attributes.ToList().ForEach(a => {
            Console.WriteLine($"\tAttribute LogicalName:{a.LogicalName}");
        });
    });
}

Output:

Entity SchemaName:Account
        Attribute LogicalName:emailaddress3
        Attribute LogicalName:emailaddress1
        Attribute LogicalName:address1_city
    <List truncated for brevity>
Entity SchemaName:Contact
        Attribute LogicalName:contactid
        Attribute LogicalName:emailaddress3
        Attribute LogicalName:emailaddress2
    <List truncated for brevity>

Create a query using EntityQueryExpression

Use EntityQueryExpression to set the RetrieveMetadataChanges Query property.

EntityQueryExpression has the following properties:

Property Type Description
Properties MetadataPropertiesExpression Set the PropertyNames to a list of property names to return. Or you can set AllProperties to true to return all the properties. For items that have them, you don't need to add the MetadataId, LogicalName, or HasChanged property names. These properties are always be included.
Criteria MetadataFilterExpression See Limit data returned using MetadataFilterExpression
AttributeQuery AttributeQueryExpression Follows the same pattern as EntityQueryExpression. AttributeQueryExpression also has Properties and Criteria to control which column definitions to return.

Note: When you use AttributeQuery, Attributes must be one of the Properties requested for the EntityQueryExpression.
RelationshipQuery RelationshipQueryExpression Follows the same pattern as EntityQueryExpression. RelationshipQueryExpression also has Properties and Criteria to control which relationship definitions to return.

Note: When you use RelationshipQuery, OneToManyRelationships, ManyToOneRelationships, or ManyToManyRelationships they must be one of the Properties requested for the EntityQueryExpression.
KeyQuery EntityKeyQueryExpression Follows the same pattern as EntityQueryExpression. EntityKeyQueryExpression also has Properties and Criteria to control which alternate key definitions to return.

Note: When you use KeyQuery, Keys must be one of the Properties requested for the EntityQueryExpression.
LabelQuery LabelQueryExpression Use theFilterLanguages property to limit the languages that are returned. If an organization has many languages provisioned, you receive labels for all languages that could add considerably to the data returned. If your app is for an individual user, you should include the user's preferred LCID language code. See Retrieve the user's preferred language code

Note

The Query parameter is optional, so you can use RetrieveMetadataChanges without any filters, but this is equivalent to using RetrieveAllEntities, a very expensive operation.

Limit data returned using MetadataFilterExpression

Use MetadataFilterExpression for the Criteria property for EntityQueryExpression, AttributeQueryExpression, RelationshipQueryExpression, and EntityKeyQueryExpression.

MetadataFilterExpression has the following properties:

Property Type Description
FilterOperator LogicalOperator Controls how the Conditions are evaluated, either And or Or.
Conditions DataCollection<MetadataConditionExpression> A collection of conditions to evaluate. See Set conditions using MetadataConditionExpression
Filters DataCollection<MetadataFilterExpression> More filters to apply for a more complex query.

Set conditions using MetadataConditionExpression

Use MetadataConditionExpression for the MetadataFilterExpression Conditions property.

MetadataConditionExpression has the following properties:

Property Type Description
ConditionOperator MetadataConditionOperator Describes the type of comparison to apply to the Value property.
PropertyName string The name of the property to evaluate
Value object The value (or values) to compare.

Generally, you can only use properties that represent simple data types, enumerations, BooleanManagedProperty, or AttributeRequiredLevelManagedProperty in a MetadataFilterExpression. You can't set conditions on any properties that are collections or labels. When a BooleanManagedProperty or AttributeRequiredLevelManagedProperty is specified, only the Value property is evaluated. Filtering on AttributeMetadata.SourceType property isn't supported.

MetadataConditionOperator Enum values

The MetadataConditionOperator Enum has the following members:

Field Description
Equals The values are compared for equality.
NotEquals The two values aren't equal.
In The value exists in a list of values.
NotIn The given value isn't matched to a value in a list.
GreaterThan The value is greater than the compared value.
LessThan The value is less than the compared value.

Process data returned

The RetrieveMetadataChangesResponse has the following properties:

Property Type Description
EntityMetadata EntityMetadataCollection The table definitions requested. When you're querying data, or when initializing a cache, this value can be treated the same as the response from the RetrieveAllEntities message. If you want to access a specific column, relationship, or alternate key definition, you must return the table definition that contains them.
ServerVersionStamp string A timestamp identifier for the metadata retrieved. When you manage a cache of schema definitions, use this value as the ClientVersionStamp property in subsequent requests so that only the changes since the previous request is returned.
DeletedMetadata DeletedMetadataCollection Data for the items deleted since the previous request. This value only contains data when RetrieveMetadataChanges is sent with the ClientVersionStamp and DeletedMetadataFilters parameters. For more information, see Cache Schema data

Retrieve the user's preferred language code

The following examples show how you can retrieve the user's preferred LCID language code.

You can retrieve the user's preferred language from the UserSettings.UILanguageId column.

static int? RetrieveUserUILanguageCode(IOrganizationService service)
{
   // To get the current user's systemuserid
   var whoIAm = (WhoAmIResponse)service.Execute(new WhoAmIRequest());

   var query = new QueryExpression("usersettings")
   {
         ColumnSet = new ColumnSet("uilanguageid", "systemuserid"),
         Criteria = new FilterExpression
         {
            Conditions = {
                  {
                     new ConditionExpression(
                        attributeName:"systemuserid",
                        conditionOperator:ConditionOperator.Equal,
                        value: whoIAm.UserId)
                  }
            }
         },
         TopCount = 1
   };

   EntityCollection userSettings = service.RetrieveMultiple(query: query);
   if (userSettings.Entities.Count > 0)
   {
         return (int)userSettings.Entities[0]["uilanguageid"];
   }
   return null;
}

See also

Cache Schema data
Web API Query schema definitions and detect changes Sample (C#)
SDK for .NET Query schema definitions and detect changes Sample (C#)
SDK for .NET: Table definitions in Microsoft Dataverse
Query table definitions using the Web API