Use FetchXML aggregation in MSCRM 2013

MS CRM offers different querying patterns to use build queries. In this module, we are going to discuss about some theory about building queries with Fetch XML and followed by demo.

With Fetch XML, you can use all standard query operators and partial values. Fetch XML is really powerful. You can do all these checks with query expressions or date related examples. You can also combine multiple conditions.

• Standard Operators Examples
  eq, neq, ne, gt, ge, le, lt, like, not-like, in, not-in, null, not-null
• Partial value Examples
  Begins-with, ends-with, not-end-with, not-begin-with
• Date Related Examples
  Today, tomorrow, last-seven-days, this-week, this-month, next-month
• This-fiscal-year, next-fiscal year
  On, on-or-before/after

Getting Related Data

• Useful to reference and include data from N:1 related entities
• We can specify filter conditions based on the related data

Using Aggregation

Unique and powerful features with Fetch XML is Aggregation

• Enable aggregates vis aggregate= true and distinct=false on <fetch tag
• Can have one or more attributes with aggregate specified
• Avg, Count, Max, Min and Sum

Examples

• Null values are not considering in computing averages, zeros are
• Null, 100, 100, 1000 would have average of 100
• Null, 100, 0, 1000 would have average of 66.66
• Null, 100, Null, 100 would have average of 100

<fetch  distinct='false' mapping ='logical' aggregate='true'>

<entity name='opportunity'>

<attribute name='estimatedvalue' aggregate='avg' alias='estimatedvalue_avg' />

<attribute name='estimatedvalue' aggregate='min' alias='estimatedvalue_min' />

<attribute name='estimatedvalue' aggregate='max' alias='estimatedvalue_max' />

</ entity>

</ fetch>

Using Grouping

Grouping is another powerful feature we can do.

• Can group on normal attributes – can’t group by virtuals
• For dates, you must specify a dategrouping. Example: Year, quarter, month, weekly or day
• Group can be specifies for Linked Entity

<fetch  distinct='false' mapping ='logical' aggregate='true'>

<entity name='opportunity'>

<attribute name='statecode' groupby='true' alias='state' />

<attribute name='createdon' groupby='true' alias='state' dategroupping='month' alias='created' />

<attribute name='estimatedvalue' aggregate='avg' alias='estimatedvalue_avg' />

<attribute name='estimatedvalue' aggregate='min' alias='estimatedvalue_min' />

<attribute name='estimatedvalue' aggregate='max' alias='estimatedvalue_max' />

</ entity>

</ fetch>

Limitations in Fetch XML

There are some limitations to Fetch XML query expression:

• No Union Support – instead you have to use multiple queries
• No concept of sub queries
• Fetch aggregates are limited to 50,000 records by default (online is limit to 50,000) AggregateQueryRecordLimit is the setting for on-premise
• Liked entities are limited to 10
• Conditions can’t be to other fields on the entity
• No way to do “or” conditions across linked entities
   
  Demo:-

1. Create the FetchXML via advance find in CRM

Query Like below:-

Click on Download FetchXML and Your XML Look like Below.

Save it as FetchXML.txt File and Open it, type Ctrl+H

And replace all the “with ‘

And your FetchXML is ready to use in the Code

2. Create a ASP.NET Empty Web Application project in Visual studio 2012.

 

3. Add the following references from the SDK\bin folder.

You can skip this step and the next by simply installing the Microsoft.CrmSdk.Extensions NuGet package.

AntiXSSLibrary.dll

Microsoft.Crm.Sdk.Proxy.dll

Microsoft.Xrm.Client.dll

Microsoft.Xrm.Portal.dll

Microsoft.Xrm.Portal.Files.dll

Microsoft.Xrm.Sdk.dll

 

4. Add the following references from .NET.

System.IdentityModel.dll

Microsoft.ServiceBus.dll

System.Data.Services.dll

System.Data.Services.Client.dll

System.Runtime.Serialization.dll

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.ServiceModel;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Client;

using Microsoft.Xrm.Sdk.Query;

5. Make sure the web application project specifies .NET Framework 4.5.2 as the target framework and not .NET Framework 4.5.2 Client Profile. Check the properties of the project by selecting the ConsoleApplication1 project in Solution Explorer and clicking Alt-Enter.

Right Click WebApplication1 -> Properties

6. Right-click the project in Visual Studio, click Add, and then click Existing Item.

Check references. “CrmServiceHelpers.cs” and “DeviceIdManager.cs” files  in CRM SDK 2013 as below:

• CrmServiceHelpers.cs
• DeviceIdManager.cs
  
  7. Add the following code to your Program.cs File
  namespace FetchXML_Aggregate
      {
      class Program
          {
          static void Main(string[] args)
              {
                  // obtain the target orgnization web address and client logon
                  Microsoft.Crm.Sdk.Samples.ServerConnection serverConnect = new Microsoft.Crm.Sdk.Samples.ServerConnection();
                  Microsoft.Crm.Sdk.Samples.ServerConnection.Configuration config = serverConnect.GetServerConfiguration();
                  OrganizationServiceProxy _serviceProxy;
                  using (_serviceProxy = Microsoft.Crm.Sdk.Samples.ServerConnection.GetOrganizationProxy(config))
                  {
                  string strAggregate = @"<fetch distinct='false' mapping='logical' output-format='xml-platform'
                                          version='1.0'>
   
                                          <entity name='contact'>
   
                                          <attribute name='fullname'/>
   
                                          <attribute name='contactid'/>
   
                                          <attribute name='address1_telephone1'/>
   
                                          <attribute name='mobilephone'/>
   
                                          <attribute name='firstname'/>
   
                                          <attribute name='ownerid'/>
   
                                          <order descending='false' attribute='fullname'/>
   
                                          <filter type='and'>
   
                                          <filter type='or'>
   
                                          <condition attribute='fullname' operator='not-null'/>
   
                                          <condition attribute='address1_telephone1' operator='not-null'/>
   
                                          <condition attribute='firstname' operator='not-null'/>
   
                                          <condition attribute='contactid' operator='not-null'/>
   
                                          <condition attribute='mobilephone' operator='not-null'/>
   
                                          </filter>
   
                                          <condition attribute='firstname' operator='eq' value='Richa'/>
   
                                          </filter>
   
                                          </entity>
   
                                          </fetch>";
   
                      var aggResults = _serviceProxy.RetrieveMultiple(new FetchExpression(strAggregate));
                      foreach (var c in aggResults.Entities)
                      {
                        string NameInfo = (string)c.Attributes["fullname"];
                        Guid ContactID = (Guid)c.Attributes["contactid"];
                            //string TelephoneInfo = null;
   
                            // if(c.Attributes.Contains("address1_telephone1"))
                            //  {
                            //    TelephoneInfo = (string)c.Attributes["address1_telephone1"];
                            //  }
   
                            // string MobileInfo = null;
   
                            //        if (c.Attributes.Contains("mobilephone"))
                            //         {
                            //          MobileInfo = (string)c.Attributes["mobilephone"];
                            //         }
   
                        string TelephoneInfo = c.GetAttributeValue<string>("address1_telephone1");
                        string MobileInfo = c.GetAttributeValue<string>("mobilephone");
                        Console.WriteLine("Name : " + NameInfo);
                        Console.WriteLine("IdentityofContact : " + ContactID);
                        Console.WriteLine("Mobile : " + MobileInfo);                     
                        Console.WriteLine("Telephone : " + TelephoneInfo);
                        Console.WriteLine("Press Enter for next record");
                        Console.ReadLine();
                      }
                  }
              }
          }
      }
  8. Testing:-Compile and Run the console Application
  
  9. Testing:
  Query Like below in CRM