Fetch XML versus Query Expression

Query Expression:

A query expression is used to search for records for a single entity type. Queries are built as an object model. This class supports all the features in FetchXML except for aggregates and grouping. It also supports both early and late bound entity programming styles.

Query Expression can only be used in server side language i.e C#.

Query Expression is class based and is easy to use, although you can only query one entity type at a time. Therefore, if you need to get data from a parent entity, such as a contact, and then get data from a child entity, such as a custom product entity, you have to perform two queries. First you must retrieve the contact to get access to its attributes, then you must retrieve the product using the contact id that links the contact to the product. With Query Expression, you can specify the attributes you want returned, or indicate you want all attributes returned, you can specify “and” conditions or ”or” conditions, and you can specify a sort order. To execute a QueryExpression query:

Call the Retrieve or Retrieve Multiple methods on the Crm Service web service
Receive a Business Entity Collection containing the results of the query

So you are not able to execute a query similar to the following

Select Opportunity.name, Opportunity.estimatedvalue, Account.address1_city from FilteredOpportunity Opportunity Inner join

FilteredAccount Account on Opportunity.customerid = Account.accountis
Where account.address1_country = “US”

Limitation of the Query Expression is , If you are Querying CRM DB using QueryExpression you cannot able to set Related Entity columns as filter criteria or to provide a column of a linked entity to be returned as the query result.

Probably because it uses the object oriented style of coding and so you have distinct classes for query, condition, columns etc. You have intelligence to support you when writing a query using Query Expression.

However, it has its own limitations, one of them being the inability to provide a column of a linked entity to be returned as the query result. This perhaps because the Query Expression would return a dynamic entity or a strongly typed base entity and so it is unable to return columns of related entity.

Example of QueryExpression:

      QueryExpression query = new QueryExpression();
      query.EntityName = EntityName.contact.ToString();
      ColumnSet columns = new ColumnSet();
      columns.Attributes =  new string[] { “contactid”, “lastname”, “firstname” };

      ConditionExpression whereClause = new ConditionExpression();
      whereClause.AttributeName = “lastname”;
      whereClause.Operator = ConditionOperator.Equal;
      whereClause.Values = new string[] { “Jones” };

      FilterExpression filter = new FilterExpression();
      filter.FilterOperator = LogicalOperator.And;
      filter.Conditions = new ConditionExpression[] { whereClause };

      OrderExpression orderBy = new OrderExpression();
      orderBy.OrderType = OrderType.Descending;
      orderBy.AttributeName = “createdon”;

      query.ColumnSet = columns;
      query.Criteria = filter;
      query.Orders = new OrderExpression[] { orderBy };

BusinessEntityCollection retrieved = crmService.RetrieveMultiple(query);

FetchXML:

Microsoft Dynamics CRM 4 provides two main mechanisms for querying data from Microsoft Dynamics CRM, FetchXML and QueryExpression. In general, it is recommended to use QueryExpression over FetchXML because of its better performance and strongly typed results. But, FetchXML is handy when you need attributes from multiple entities in a single query.

FetchXML requires the query to be specified in XML format and the result set is returned in XML format as well. This allows FetchXML to return related entity columns as well as this is just another node in the xml doc.

With FetchXML you can query multiple entity types at the same time as long as there is a relationship between the types. You can retrieve attributes from multiple entities in one query in this fashion. To execute a FetchXML query:

This is a custom XML-based query language that supports all the features of QueryExpression plus aggregates and grouping. Fetch queries can return records for multiple entities. In addition, queries can be serialized and saved in the database. This is used to save a query as a user-owned saved view in the User Query entity and as an organization-owned saved view in the Saved Query entity. The fetch string contains XML, which specifies the query criteria

Use this query language when you need support for aggregates and grouping or you need to save the query.

The result was FetchXML is faster than the query expression.

The only thing I hate about FetxhXML is that it's hard to build, unlike the query expression.

Additionally, back when MSCRM 4.0 was out and this feature was not available,

But Fetch Xml query can only be used in SSRS, JavaScript (Webapi) and C#.

Create a fetch string specifying the query and pass it to the CrmService web service’s fetch method
The fetch string contains XML, which specifies the query criteria.

Example of FetchXML:

string fetch = @”
   <fetch mapping=””logical””>
       <entity name=””contact””>
            <attribute name=””contactid””/>
            <attribute name=””lastname””/>
            <attribute name=””firstname””/>
            <order attribute=””createdon””/>
                 <filter>
                     <condition attribute=””lastname”” operator=””eq””
                        value=””Jones””/>
                 </filter>
         </entity>
   </fetch>”;
   try
{
           string result = service.Fetch(fetch);
   }
   catch(System.Web.Services.Protocols.SoapException se)
   {
            // handle exception
   }

https://blog.edgewater.com/2010/07/15/fetchxml-versus-query-expression/

Converting Fetch to Query Expression And Query Expression to Fetch:

FetchXmlToQueryExpressionRequest ConversionRequest = new FetchXmlToQueryExpressionRequest();

QueryExpression ConversionResponse = Service.FetchXmlToQueryExpression(Estimatedvalue) as QueryExpression ;

EntityCollection QueryExpressionResults = Service.RetrieveMultiple(ConversionResponse);

foreach (Entity queryExpressionResult in QueryExpressionResults.Entities)

    Console.WriteLine("Opportunityid:{0}", queryExpressionResult["opportunityid"].ToString());

Console.WriteLine("Total Opportunities from Converted QueryExpression: {0}", QueryExpressionResults.Entities.Count.ToString());

// Create the request object.

QueryExpressionToFetchXmlRequest expression = new QueryExpressionToFetchXmlRequest();

// Set the properties of the request object.

expression.Query = query;

// Execute the request.

QueryExpressionToFetchXmlResponse queried = (QueryExpressionToFetchXmlResponse) service.Execute(expression);

Search This Blog

apycrm