today I want to write few words about Quering CRM data. I discovered as started FetchXML.
Initially I was ver sceptical about xml for quering data. First of all because of debugging xml is something that I dislike, also I prefer SQL or Linq, and was quite puzzled, why we can need FetchXML.
But with time I realized that with FetchXML you can easily do grouping operations on Dynamics CRM data. And that is something that can make life of developer easier.
Consider following xml for FetchXML:
<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='opportunity'> <attribute name='statecode' groupby='true' alias='state' /> <attribute name='estimatedvalue' aggregate='avg' alias='estimated_avg' /> <attribute name='estimatedvalue' aggregate='min' alias='estimated_min' /> <attribute name='estimatedvalue' aggregate='max' alias='estimated_max' /> </entity> </fetch>
here important part is setting distinct to false. Then you'll get grouping by estimatedvalue, and provide aggregate function, and so on.
Also FetchXML has followign limitations:
1. No Union
2. No sub queries
3. by default aggregation is limited with 50 000 records.
4. Conditions can't be to other fields in entity
5. No way to do or across linked entities.