Query data using the SDK for .NET

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

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:

1. 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);

2. 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:

• Page results using FetchXml
• Page results using QueryExpression

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:

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

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:

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

Learn more about column aliases:

• FetchXMl Column aliases
• QueryExpression 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.

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
};