Thursday, 3 July 2014

Difference between QueryByAttribute vs QueryExpression vs FetchExpression

In this post, I am going to explain the difference between QueryByAttribute vs QueryExpression vs FetchExpression and the suggestion of when to use which one.

I just added QueryByAttribute in this post just because it is one of the class which inherits from QueryBase but obviously, it is used for simple one entity query with one or more equal conditions which are combined using "AND". So, if your query is very simple enough with such limitation, you may use it instead of QueryExpression to save entering ConditionExpression, ConditionOperator.Equal and stuffs. But there is one more limitation that there must be at least one attribute condition to use QueryByAttribute. So, you cannot use it to count all records of a particular entity either. Other than that, there is nothing else to mention about QueryByAttribute since I will be more emphasising about the difference between QueryExpression and FetchExpression.

Starting from CRM 2011 SDK, we are able to use FetchExpression which allows us to query using Fetch XML with RetrieveMultiple. So basically, we are able to perform complex queries (different conditions, link entities, etc.) using both QueryExpression and FetchExpression (except aggregate queries for which you can only use FetchExpression)

So, along my development, I just tried to use them alternately depending on the complexity of the query. Personally, I prefer to use FetchExpression for those queries with two or more link entities or I need to query the value in the link entities because I found them cleaner and more readable (but that’s my personal opinion)

Other than that readability, the only difference that I found between QueryExpression and FetchExpression is about ColumnSet specification and return column. With QueryExpression, if you don't specify ColumnSet, the result entities will contain only one attribute which is the GUID key column. But in FetchExpression, if there is no <attribute> tag specified in the fetch XML, the result set returns all attributes which is equivalent to using <all-attributes /> in the XML.
So, if the query is just to get the ID of the entities or to get the count, etc. and no column need to be specified, at least add the <attribute> tag with the primary GUID key column (as in the following XML) so that the query will not fetch all columns and the query will be performed faster.

 <fetch mapping='logical'>  
  <entity name='contact'>  
   <attribute name='contactid' />  

Other than that, I still haven't found any difference since we are able to query the Columns of the linkentity, specify the EntityAlias of the link entity in QueryExpression as well. So, if you know something else which is not mentioned in this post, feel free to leave a comment.

No comments:

Post a Comment