QueryByAttribute, QueryExpression and FetchExpression in Dynamics CRM

QueryByAttribute and QueryExpression are classes to query the data from CRM. Below are some of the differences we have:

QueryByAttribute:

• QueryByAttribute class is a simple class compared to QueryExpression class. Hence, we can select QueryByAttribute if the query is simple.
• No need to enter the conditions expression, condition operators while designing the criteria for QueryByAttribute.
• It queries the CRM data from specified entity by specifying criteria with set of attributes and value pairs.
• It automatically consider “AND” between specified attributes as it doesn’t support “OR”.
• It supports only “Equal” condition operator

Example:

It retrieves all the accounts which are related to account name.

QueryByAttribute querybyexpression = new QueryByAttribute(“account”);
querybyexpression.ColumnSet = new ColumnSet(“name”, “address1_city”, “emailaddress1”);

// Attribute to query
querybyexpression.Attributes.AddRange(“name”);

// Value of queried attribute to return
querybyexpression.Values.AddRange(“Peter”);

QueryExpression

• QueryExpression is an object oriented, strongly typed approach to developing queries against the CRM database.
• We need to specify the conditionExpression and Conditiion Operators while designing the queries.
• QueryExpression is preferable if we want to query the CRM data with complex conditions.
• It supports the complex conditions with “AND” and “OR” operators
• It supports different condition operators like “Beginswith, Doesn’tbeginwith, Endswith,…”
• It supports to retrieve the data based on Link entities.

Example: Below query retrieves all the accounts their names starts with “MS” and related to India country.

QueryExpression queryExp = newQueryExpression(“account”);

queryExp.ColumnSet = new ColumnSet(true);

queryExp.Criteria.AddCondition(“name”, ConditionOperator.BeginsWith, “MS”);

queryExp.Criteria.AddCondition(“new_country”, ConditionOperator.Equal, “India”);

EntityCollection retrievedAccounts=(EntityCollection)OPCrmService.RetrieveMultiple(queryExp);

Fetch Expression:

• It is very clear and readable compare to Query Expression
• It is possible to query the aggregate operation with FetchExpression like Count, Sum, AVG, Min and Max
• If we miss to specify the <attribute> tag in Fetch Expression, it will return all columns
• It is advisable to create a query from advanced find and then download the fetch query and modify if required.

Important points to remember for Fetch Expression and Query Expression:

• Can link with child entities to retrieve the data
• Can group the conditions in filter criteria by using grouped AND / OR
• Can query maximum 5000 records in a single call
• Can use Paging cookie to retrieve more records.