Introduction
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
Here, we are going to build out an aggregate query against the entity called Account
.
- Goto File >> Click on New >> Click on Project >> Select console application.
- Check references. “CrmServiceHelpers.cs” and “DeviceIdManager.cs” files you can fine in CRM SDK 2013 or find below:
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;
namespace FetchXML_Aggregate
{
class Program
{
static void Main(string[] args)
{
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 mapping='logical' aggregate='true' distinct='false' >
<entity name='account'>
<attribute name='creditlimit' alias='creditLimitAvg' aggregate='avg' />
</entity>
</fetch>";
var aggResults = _serviceProxy.RetrieveMultiple(new FetchExpression(strAggregate));
foreach (var c in aggResults.Entities)
{
decimal creditLimitAvg = ((Money)c.GetAttributeValue
<AliasedValue>("creditLimitAvg").Value).Value;
Console.WriteLine("Total : " + decimal.Floor(creditLimitAvg));
Console.Read();
}
}
}
}
}
- Press F5.
You can get average of creditlimit
.