Query data using the SDK for .NET

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

Method Advantages
FetchExpression Use the proprietary FetchXML query language to create complex queries that return aggregates such as the sum of a value for all returned records. You can also perform group by operations with FetchXML. Can include data from linked table rows (entity records).
QueryExpression You have a strongly typed object model to construct complex queries. Supports all the features in FetchXML except for aggregates and grouping. Can include data from linked table rows (entity records).
QueryByAttribute A simpler object model than QueryExpression. Use QueryByAttribute for queries where you are testing whether all the table column (attribute) value criteria in your query are a match. Can only return data from a single table (entity type).
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 topic will focus on the styles of queries available via the SDK assembly classes. More information: Build queries with LINQ (.NET language-integrated query)

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:

Note

The IOrganizationService.RetrieveMultiple method is generally preferred. There are no special capabilities that require the use of the RetrieveMultipleRequest class.

Both of these methods will return an EntityCollection that contains the results of the query in the Entities collection as well as properties to manage additional queries to receive paged results.

Note

To ensure best performance, each query request can return a maximum of 5000 rows. To return larger result sets you must request additional pages.

All filter conditions for string values are case insensitive.

Null table column values are not returned

When a table column (entity attribute) contains a null value, or if the attribute was not included in the FetchXml attributes or the ColumnSet, the Entity.Attributes collection will not include the attribute. There will be neither a key to access it or a value to return. The absence of the attribute indicates that it is null. When using the early bound style, the generated Entity class properties will manage this and return a null value.

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

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

  1. For an attribute that could be null, use the Entity.Contains(String) method to check whether the attribute 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>(String) to access the value. For example:

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

Note

If the type specified with GetAttributeValue<T>(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.

Use FetchXML with FetchExpression

FetchXml is a proprietary XML-based query language that can be used with SDK Assembly queries using FetchExpression and by the Web API using the fetchXml query string. More information: Query data using FetchXml

The following example shows a simple query to return up to 50 matching account rows where the address1_city value equals Redmond, ordered by name.

string fetchXml = @"
<fetch top='50' >
  <entity name='account' >
    <attribute name='name' />
    <filter>
      <condition 
        attribute='address1_city' 
        operator='eq' 
        value='Redmond' />
    </filter>
    <order attribute='name' />
  </entity>
</fetch>";

var query = new FetchExpression(fetchXml);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x => {
  Console.WriteLine(x.Attributes["name"]);
});

Important

When retrieving table rows you should only request the column values you need by setting the specific attributes using attribute elements rather than using the all-attributes element to return all attributes.

More information:

Use QueryExpression

The QueryExpression class provides a strongly typed set of objects that is optimized for run-time manipulation of queries.

The following example shows a simple query to return up to 50 matching account rows where the address1_city value equals Redmond, ordered by name.

var query = new QueryExpression("account")
{
  ColumnSet = new ColumnSet("name"),
  Criteria = new FilterExpression(LogicalOperator.And),
  TopCount = 50
};
query.Criteria.AddCondition("address1_city", ConditionOperator.Equal, "Redmond");
query.AddOrder("name", OrderType.Ascending);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x =>
{
  Console.WriteLine(x.Attributes["name"]);
});

Important

When retrieving rows you should only request the column values you need by setting the specific attributes using the ColumnSet class constructor. Although ColumnSet class constructor provides an overload that accepts a boolean allColumns parameter, you should not use this in production code.

More information:

Use QueryByAttribute

The QueryByAttribute class provides a strongly typed set of objects that is optimized for simple, common queries of table rows. Unlike FetchXML and QueryExpression, QueryByAttribute can only return data from a single table. It doesn't enable retrieving data from related table rows or complex query criteria.

The following example shows a simple query to return up to 50 matching account rows where the address1_city value equals Redmond, ordered by name.

var query = new QueryByAttribute("account")
{
  TopCount = 50,
  ColumnSet = new ColumnSet("name")
};
query.AddAttributeValue("address1_city", "Redmond");
query.AddOrder("name", OrderType.Ascending);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x =>
{
  Console.WriteLine(x.Attributes["name"]);
});

More information:

Access formatted values

Regardless of the method you use to query tables, the data will be returned as EntityCollection.Entities. You can access the table column (attribute) data values using the Entity.Attributes collection. But these values may be of type other than string which you would need to manipulate to get string values you can display in your application.

You can access string values that use the environments settings for formatting by using the values in the Entity.FormattedValues collection.

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

Attribute logical name Type
primarycontactid EntityReference
createdon DateTime
revenue Money
statecode OptionSetValue
var query = new QueryByAttribute("account")
{
TopCount = 50,
ColumnSet = new ColumnSet("name", "primarycontactid", "createdon", "revenue", "statecode")
};
query.AddAttributeValue("address1_city", "Redmond");
query.AddOrder("name", OrderType.Ascending);

EntityCollection results = svc.RetrieveMultiple(query);

results.Entities.ToList().ForEach(x =>
{
Console.WriteLine(@"
name:{0}
primary contact: {1}
created on: {2}
revenue: {3}
status: {4}",
  x.Attributes["name"],
  (x.Contains("primarycontactid")? x.FormattedValues["primarycontactid"]:string.Empty),
  x.FormattedValues["createdon"],
  (x.Contains("revenue") ? x.FormattedValues["revenue"] : string.Empty),
  x.FormattedValues["statecode"]
  );
});

Note

Table columns (attributes) which contain null values are not returned in the query Attributes or FormattedValues collections. If a column may contain a null value you should check using the Contains method before attempting to access the value.

The formatted results would display like these:

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

name:City Power & Light (sample)
  primary contact: Scott Konersmann (sample)
  created on: 2/28/2018 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

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.