How to use Group by with linked entity in fetchxml

In this article we will learn How to use Group by with linked entity in fetchxml ?

In this example shows how to use the sum“aggregate attribute to sum linked entity values.

FetchXML includes grouping and aggregation features that you can use this function calculate sum, average min, max and count.

Information About aggregation

For create an aggregate attribute, set the keyword aggregate to true, then specify entity name, attribute name and mandatory to alias(variable name). You must also specify the type of aggregation you want to perform.

In this example you can see alias name is “total_count” and type of aggregation is countcolumn and used here EntityCollection and RetrieveMultiple.

EntityCollection : EntityCollection is used for Contains a collection of entity instances.
RetrieveMultiple :RetrieveMultiple is used for Retrieves a collection of records.

string example=
@"<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='name' alias='total_count' aggregate='countcolumn' />
<link-entity name='systemuser' from='systemuserid' to='ownerid'>
<attribute name='parentsystemuserid' alias='managerid' groupby='true' />
</link-entity>
</entity>
</fetch>";

EntityCollection result = _serviceProxy.RetrieveMultiple(new FetchExpression(example));
foreach (var item in result.Entities)
{
int? aggregate1 = (int?)((AliasedValue)item["count"]).Value;
System.Console.WriteLine("Count of all opportunities: " + aggregate1 + "\n");
}