Using Aggregate Functions with QueryExpression in Dynamics 365

Introduction

In Microsoft Dataverse (Power Apps), there is often a need to retrieve summarized or aggregated data, such as counts, averages, sums, minimums, or maximums, rather than retrieving every individual record.

Traditional query methods using QueryExpression focus on row-level data retrieval and do not inherently support aggregate functions. This creates challenges for developers who need to generate meaningful business insights directly from server-side code or plugins.

How can developers leverage the QueryExpression class to perform aggregate queries efficiently within the constraints of the Dataverse platform?

We’ll walk through the process of performing aggregate operations using QueryExpression within Microsoft Dataverse. We’ll demonstrate how to implement server-side aggregation with the help of QueryExpression.

Why use Aggregate data using QueryExpression?

Using aggregate data with QueryExpression in C# allows you to retrieve summarized insights like the total number of active accounts, average revenue, or maximum order value directly from Dataverse. This is efficient for custom reports, dashboards, or automated tools, as it reduces data transfer and avoids processing large datasets in memory.

Prerequisites for Performing Aggregate Queries Using QueryExpression

• Ensure that .NET Framework version 4.6.2 or later is installed to support integration with Dynamics 365 SDK libraries.
• Include references to the following required SDK libraries:
  Xrm.Sdk and Microsoft.Crm.Sdk.Proxy.
• Use of QueryExpression with ColumnSet, AttributeExpressions, and proper aliasing for aggregation.
• Aggregation must be explicitly enabled by setting QueryExpression.ColumnSet and configuring QueryExpression Criteria accordingly.

Steps to Perform Aggregate Queries Using QueryExpression

Step 1: Set Up Your Environment

Create an authenticated IOrganizationService instance.

Step 2: Create a QueryExpression with Aggregation Enabled

This query retrieves:

• Average revenue across all active accounts owned by a specific user.
• Total number of revenue records.
• Minimum and maximum revenue values among the filtered results.

This approach minimizes network bandwidth usage and improves performance by pushing the aggregation logic to the server level.

Note: This functionality is supported in SDK versions targeting Microsoft Dataverse (via Xrm.Sdk), and requires usage of XrmAttributeExpression and XrmAggregateType, which are available in recent SDK packages.

var query = new QueryExpression("account") // Query on Account entity

{

ColumnSet = new ColumnSet(false) // No regular fields, only aggregate expressions

{

AttributeExpressions = {

{

new XrmAttributeExpression(attributeName : "revenue", alias : "Average", aggregateType : XrmAggregateType.Avg) },  // Average revenue

{

new XrmAttributeExpression(attributeName : "revenue", alias :"Count", aggregateType : XrmAggregateType.Count) }, // Count of records

{ new XrmAttributeExpression(attributeName : "revenue", alias :"Min", aggregateType : XrmAggregateType.Min) }, // Minimum revenue

{ new XrmAttributeExpression(attributeName : "revenue", alias :"Max", aggregateType : XrmAggregateType.Max) } // Maximum revenue

}

},

Criteria = new FilterExpression(LogicalOperator.And) // Filtering conditions

{

Conditions =

{

new ConditionExpression("statecode", ConditionOperator.Equal, 0), // Only active accounts

new ConditionExpression("ownerid", ConditionOperator.Equal, new Guid("03b8f351-a246-f011-8779-7c1e520e8711")) // Specific owner

}

}

};

var results = service.RetrieveMultiple(query); // Execute the query

if (results != null && results.Entities != null & results.Entities.Count > 0)

{

Entity information = results.Entities[0];

Console.WriteLine("********************* Revenue Generated By USER 1 ********************");

Console.WriteLine($"********************* Average Revenue {((Money)((AliasedValue)information.Attributes["Average"]).Value).Value} ********************");

Console.WriteLine($"********************* Count Revenue {((AliasedValue)information.Attributes["Count"]).Value} ********************");

Console.WriteLine($"********************* Min Revenue {((Money)((AliasedValue)information.Attributes["Min"]).Value).Value} ********************");

Console.WriteLine($"********************* Max Revenue {((Money)((AliasedValue)information.Attributes["Max"]).Value).Value} ********************");

}

else

{

Console.WriteLine("No Information Found");

}

When the above code is executed, it fetches aggregate data for the revenue field for active accounts owned by a specific user. The final output may appear as follows:

Explanation

• Average Revenue: The mean value of all revenue fields from active account records owned by the specified user.
• Count Revenue: Total number of account records considered for aggregation.
• Min Revenue: The smallest revenue value among those records.
• Max Revenue: The largest revenue value among those records.

Why It Matters

Using QueryExpression for aggregation:

• Reduces round-trips to the server by returning only summarized results.
• Minimizes memory usage by avoiding the need to retrieve and process full datasets in code.
• Enhances performance for custom reporting tools, dashboards, or integration scenarios.

Conclusion

Leveraging QueryExpression for aggregate data retrieval in C# offers a powerful, code-centric way to perform real-time analytics within Dynamics 365. While it supports essential aggregate functions like SUM, AVG, MIN, MAX, and COUNT, developers should be aware of its limitations—such as the lack of support for DISTINCT counts, cross-entity aggregation, and large dataset boundaries.

By combining the power of QueryExpression with post-processing in C# (e.g., LINQ), you can overcome most of these limitations without resorting to FetchXML. This keeps your codebase clean, strongly typed, and aligned with traditional .NET development practices, making it ideal for plugins, custom workflows, and background services within the Dataverse ecosystem.

The post Using Aggregate Functions with QueryExpression in Dynamics 365 first appeared on Microsoft Dynamics 365 CRM Tips and Tricks.

Original Post https://www.inogic.com/blog/2025/06/using-aggregate-functions-with-queryexpression-in-dynamics-365/