Dataverse Group by Attribute in Fetchxml C#

It’s been while since I have written any blog. Today blog will be on quick tip to group attribute and get result in c# using Fetch xml and Linq.

While working on one of the requirement I was asked to get the data from CRM and group it by Owner. I have used below code to achieve my requirement which can be helpful for you as well.

 string queryRoomAllocDetails = $@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                                  <entity name='crfdf_roomallocationdetails'>
                                                    <attribute name='crfdf_roomallocationdetailsid' />
                                                    <attribute name='crfdf_name' />
                                                    <attribute name='createdon' />
                                                    <attribute name='statuscode' />
                                                    <attribute name='statecode' />
                                                    <attribute name='crfdf_room' />
                                                    <attribute name='crfdf_rentpermonth' />
                                                    <attribute name='overriddencreatedon' />
                                                    <attribute name='ownerid' />
                                                    <attribute name='modifiedon' />
                                                    <attribute name='modifiedonbehalfby' />
                                                    <attribute name='modifiedby' />
                                                    <attribute name='crfdf_lastpaymentdate' />
                                                    <attribute name='crfdf_isvacant' />
                                                    <attribute name='crfdf_isnotify' />
                                                    <attribute name='createdonbehalfby' />
                                                    <attribute name='createdby' />
                                                    <attribute name='crfdf_contacts' />
                                                    <attribute name='crfdf_contact' />
                                                    <attribute name='crfdf_allocatedto' />
                                                    <attribute name='crfdf_allocateddate' />
                                                    <order attribute='crfdf_name' descending='false' />
                                                  </entity>
                                                </fetch>";

                EntityCollection roomAllocDetails = svc.RetrieveMultiple(new FetchExpression(queryRoomAllocDetails));

                var responseRoomAllocDetails=roomAllocDetails.Entities.GroupBy(x => x.Attributes["ownerid"]).Select(x => x);

                foreach (var data in responseRoomAllocDetails)
                {
                    Console.WriteLine(((EntityReference)data.Key).Name);
                    foreach (var result in data)
                    {
                        Console.WriteLine(result.GetAttributeValue<string>("crfdf_name"));
                    }
                }

FetchXML OOB does suppport grouping but you can only fetch fields which are part of the grouping and aggregate function (Min, Max, Count) applied attributes only.

Hope this helps!

Advertisement