Query data using the SDK for .NET

發行項
05/25/2024

The SDK for .NET provides several methods to query data. Each provides different advantages.

Method	Advantages
FetchExpression class	Use the proprietary FetchXML query language to create complex queries that can return paged data sets or grouped and aggregated data. You can create joins to include data from related records. FetchXml provides capabilities that other options don't. Learn how to query data using FetchXml
QueryExpression class	Use a strongly typed object model to create complex queries that can return paged data sets or grouped and aggregated data. You can create joins to include data from related records. Supports most the features in FetchXML. Learn how to query data using QueryExpression
QueryByAttribute class	A simpler object model for common queries to return rows that match all the criteria in your query. Supports paging, but not groups and aggregated data sets. Can only return data from a single table. Learn how to query data using the QueryByAttribute class
LINQ	Use OrganizationServiceContext.QueryProvider to compose queries using the popular LINQ syntax. All LINQ queries are converted to QueryExpression so the capabilities are limited to those available to `QueryExpression`. This article focuses on SDK classes to retrieve data. Learn how to query data with LINQ (.NET language-integrated query)

How to send requests

FetchExpression, QueryExpression, and QueryByAttribute derive from the QueryBase abstract class. There are two ways to get the results of a query defined using these classes:

You can pass an instance of any of these classes as the query parameter to IOrganizationService.RetrieveMultiple method.
You can set the RetrieveMultipleRequest.Query property of the class and use the IOrganizationService.Execute method.

Generally, people use the IOrganizationService.RetrieveMultiple method, but you might use the RetrieveMultipleRequest class to use optional parameters or to send the request as part of a batch using the ExecuteMultipleRequest or ExecuteTransactionRequest classes.

Both of these methods return an EntityCollection that contains the results of the query in the Entities collection property. EntityCollection has other properties to manage paging results returned.

When you retrieve data using these classes there are some concepts you must understand. The rest of this article explains common concepts when retrieving data using the SDK for .NET classes.

Null column values are not returned

When a table column contains a null value, or if the column wasn't requested, the Entity.Attributes collection won't include the value. There isn't a key to access it or a value to return. The absence of the attribute indicates that it's null.

Columns that are not valid for read always return null values. The definition of these columns have the AttributeMetadata.IsValidForRead property set to false.

Early bound classes manage null values

When you use the early bound style, the properties of the generated classes that inherit from Entity class manage this and return a null value. Learn about generating early bound classes

How to mitigate null values using late bound classes

When you use the late bound style, if you try to access the value using an indexer on the Entity.Attributes or Entity.FormattedValues collections, you'll get an KeyNotFoundException with this message: The given key was not present in the dictionary.

To avoid this problem when using the late-bound style, you can use two strategies:

For an column that could be null, use the Entity.Contains(System.String) method to check whether the column value is null before attempting to access it with an indexer. For example:

Money revenue = (entity.Contains("revenue")? entity["revenue"] : null);
Use Entity.GetAttributeValue<T>(System.String) method to access the value. For example:

Money revenue = entity.GetAttributeValue<Money>("revenue");

Note

If the type specified with Entity.GetAttributeValue<T>(System.String) is a value type that cannot be null, such as Boolean or DateTime, the value returned will be the default value, such as false or 1/1/0001 12:00:00 AM rather than null.

Each request can return up to 5000 records

Interactive applications will typically limit the number of records displayed to a number that a human can interact with, and then provide the option to navigate pages of data. For example, model-driven apps depend on a personal option that allows people to choose a value from 25 to 250. This information is stored in the UserSettings.PagingLimit column.

Applications that retrieve data from Dataverse without displaying data in an app don't need to specify a page size. The default and maximum page size is 5,000 rows. If you don't set a page size, Dataverse will return up to 5,000 rows of data at a time. To get more rows, you must send additional requests.

Paging works best when you use the paging cookie data that Dataverse returns with the EntityCollection.PagingCookie property, but it isn't required and some requests will not return a paging cookie value. Learn more:

Formatted values are returned for some columns

For each Entity in the EntityCollection.Entities, access the table column (attribute) data values using the Entity.Attributes collection.

You can display and edit simple data types like numbers and strings in applications directly. For certain data types, Dataverse provides read-only, formatted string values you can display in applications. The format of some of these string values depend on settings that can be set by an administrator and overridden by each user.

An administrator can customize default regional options that apply for all new users. These settings are stored in the Organization table.
Each user may override these settings for their personal preferences. These settings are stored in the UserSettings table.

Use the Entity.FormattedValues collection to access formatted values for these types of columns:

Type	Data type returned	Formatted value description
Yes/No BooleanAttributeMetadata	Boolean	The localized label for the corresponding BooleanOptionSetMetadata.FalseOption or BooleanOptionSetMetadata.TrueOption properties.
Customer, Lookup, and Owner LookupAttributeMetadata	EntityReference	The EntityReference.Name value, which is the value of the primary name column for the record.
Date and Time DateTimeAttributeMetadata	DateTime	Depends on the behavior and format configurations for the column, organization settings, and personal options set by the user, such as the time zone they are in.
Entity Name EntityNameAttributeMetadata	String	When the value isn't `none`, the formatted value is the localized DisplayName value for the table.
Currency MoneyAttributeMetadata	Money	Depends on the currency selected for the column as well as organization and user preferences.
Choices MultiSelectPicklistAttributeMetadata	OptionSetValueCollection	When a single option is selected, the localized label for the selected option. When multiple options are selected, a string with the localized labels for each selected option, separated by `;` . For example: `Appetizer; Entree; Dessert`
Choice PicklistAttributeMetadata Status StateAttributeMetadata Status Reason StatusAttributeMetadata	OptionSetValue	The localized label for the selected option.

The following sample shows how to access the formatted string values for the following account columns:

Logical name	Type
`primarycontactid`	EntityReference
`createdon`	DateTime
`revenue`	Money
`statecode`	OptionSetValue

static void FormattedValuesExample(IOrganizationService service)
{
    List<string> columns = new() {
        "name",
        "primarycontactid",
        "createdon",
        "revenue",
        "statecode"
    };

    QueryExpression query = new("account")
    {
        ColumnSet = new ColumnSet(columns.ToArray()),
        TopCount = 3
    };

    EntityCollection accounts = service.RetrieveMultiple(query);

    accounts.Entities.ToList().ForEach(x =>
    {
        string name = (string)x.Attributes["name"];
        string primarycontactid = x.Contains("primarycontactid") ? 
           x.FormattedValues["primarycontactid"] : 
           string.Empty;
        string createdon = x.FormattedValues["createdon"];
        string revenue = x.Contains("revenue") ? 
           x.FormattedValues["revenue"] : 
           string.Empty;
        string statecode = x.FormattedValues["statecode"];

        Console.WriteLine(@$"
name:{name}
    primary contact: {primarycontactid}
    created on: {createdon}
    revenue: {revenue}
    status: {statecode}"
            );
    });
}

The formatted results would display like these:

name:A Datum (sample)
  primary contact: Rene Valdes (sample)
  created on: 2/28/2020 11:04 AM
  revenue: $10,000.000
  status: Active

name:City Power & Light (sample)
  primary contact: Scott Konersmann (sample)
  created on: 2/28/2024 11:04 AM
  revenue: $100,000.000
  status: Active

name:Contoso Pharmaceuticals (sample)
  primary contact: Robert Lyon (sample)
  created on: 2/28/2018 11:04 AM
  revenue: $60,000.000
  status: Active

Columns that use an an alias return an AliasedValue

When you retrieve aggregated values, you need to specify an name for the column that contains the aggregated value. You can also specify a different column names for 'regular' queries, although this is less common.

When you specify an alias, the value returned is wrapped in an AliasedValue. The AliasedValue class has three properties:

Property	Type	Description
`EntityLogicalName`	`String`	The logical name of the table that has the column that the data came from.
`AttributeLogicalName`	`String`	The logical name of the column that the data came from.
`Value`	`Object`	The aggregated value or the value of the column row using an alias.

When you use a column alias, you need to cast the Value property to access the value returned.

Learn more about column aliases:

Convert queries between FetchXml and QueryExpression

You can convert QueryExpression queries to FetchXml and FetchXml queries to QueryExpression using the QueryExpressionToFetchXmlRequest and FetchXmlToQueryExpressionRequest classes.

Note

There are some FetchXml capabilities that QueryExpression doesn't have. When converting a FetchXml query to QueryExpression, these differences are lost. Learn more about limitations for QueryExpression

The SavedQuery table stores system views for a table (entity type) and the UserQuery table stores saved user queries. Other tables may also store a query as a FetchXml string. These methods enable converting a FetchXml string to QueryExpression so it can be manipulated using the object model and then converted back to FetchXml so it can be saved as a string.

More information: Sample: Convert queries between Fetch and QueryExpression

Query Condition Limits

Dataverse has a limit of 500 total conditions allowed in a query. Any joins included in the query are counted as part of this limit. If a query (and its joins) exceeds 500 conditions, the user will receive the following error when the query is executed: Number of conditions in query exceeded maximum limit..

If this occurs a user must either:

Reduce the number of conditions in their query.
Use the In clause, which allows GUIDs and strings up to 850 characters with no limit on integers.

All filter conditions for string values are case insensitive

When comparing string values, don't worry about the case. The following QueryExpression query will return account records with the name Contoso, Ltd and CONTOSO, LTD.

QueryExpression query = new("account")
{
   ColumnSet = new ColumnSet("name"),
   Criteria = new FilterExpression(LogicalOperator.And) { 
      Conditions = {
         { 
               new ConditionExpression(
                  attributeName: "name", 
                  conditionOperator: ConditionOperator.Equal, 
                  value: "CONTOSO, LTD") 
         }
      }
   },
   TopCount = 3
};

共用方式為