Query data using the Organization service

The SDK assemblies for the organization service provide several styles to query data. Each provides different advantages.

Style 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 entities.
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 entities.
QueryByAttribute A simpler object model than QueryExpression. Use QueryByAttribute for queries where you are testing whether all the attribute value criteria in your query are a match. Can only return data from the entity.
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 entity records. To return larger result sets you must request additional pages.

Null attribute values are not returned

When an 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: Web API : Retrieve and execute predefined queries > Use custom FetchXML

The following example shows a simple query to return up to 50 matching account entities 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 entity records you should only request the attributes 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 entities 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 entity records you should only request the attributes 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 an entity. Unlike FetchXML and QueryExpression, QueryByAttribute can only return data from the entity. It doesn't enable retrieving data from related entities or complex query criteria.

The following example shows a simple query to return up to 50 matching account entities 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 entities, the data will be returned as EntityCollection.Entities. You can access the 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

Attributes which contain null values are not returned in the query Attributes or FormattedValues collections. If an attribute 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 entity stores system views for an entity and the UserQuery entity stores saved user queries. Other entities 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