{"id":41568,"date":"2025-06-20T11:37:02","date_gmt":"2025-06-20T06:07:02","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=41568"},"modified":"2025-06-20T11:37:02","modified_gmt":"2025-06-20T06:07:02","slug":"using-aggregate-functions-with-queryexpression-in-dynamics-365","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2025\/06\/using-aggregate-functions-with-queryexpression-in-dynamics-365\/","title":{"rendered":"Using Aggregate Functions with QueryExpression in Dynamics 365"},"content":{"rendered":"<h3><img decoding=\"async\" class=\"alignnone size-full wp-image-41574\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365.png\" alt=\"Using Aggregate Functions with QueryExpression in Dynamics 365\" width=\"1925\" height=\"1100\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365.png 1925w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365-300x171.png 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365-1024x585.png 1024w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365-768x439.png 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365-1536x878.png 1536w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/Using-Aggregate-Functions-with-QueryExpression-in-Dynamics-365-660x377.png 660w\" sizes=\"(max-width: 1925px) 100vw, 1925px\" \/><\/h3>\n<h3><strong>Introduction<\/strong><\/h3>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>How can developers leverage the QueryExpression class to perform aggregate queries efficiently within the constraints of the Dataverse platform?<\/p>\n<p>We\u2019ll walk through the process of performing aggregate operations using QueryExpression within Microsoft Dataverse<strong>.<\/strong> We&#8217;ll demonstrate how to implement server-side aggregation with the help of QueryExpression.<\/p>\n<h3><strong>Why use Aggregate data using QueryExpression?<\/strong><\/h3>\n<p>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.<\/p>\n<h3><strong>Prerequisites for Performing Aggregate Queries Using QueryExpression<\/strong><\/h3>\n<ul>\n<li>Ensure that .NET Framework version 4.6.2 or later is installed to support integration with Dynamics 365 SDK libraries.<\/li>\n<li>Include references to the following required SDK libraries:<br \/>\nXrm.Sdk and Microsoft.Crm.Sdk.Proxy.<\/li>\n<li>Use of QueryExpression with ColumnSet, AttributeExpressions, and proper aliasing for aggregation.<\/li>\n<li>Aggregation must be explicitly enabled by setting QueryExpression.ColumnSet and configuring QueryExpression Criteria accordingly.<\/li>\n<\/ul>\n<h3><strong>Steps to Perform Aggregate Queries Using QueryExpression<\/strong><\/h3>\n<h4><strong>Step 1: Set Up Your Environment<\/strong><\/h4>\n<p>Create an authenticated IOrganizationService instance.<\/p>\n<h4><strong>Step 2: Create a QueryExpression with Aggregation Enabled<\/strong><\/h4>\n<p>This query retrieves:<\/p>\n<ul>\n<li><strong>Average revenue<\/strong> across all active accounts owned by a specific user.<\/li>\n<li>Total number of revenue records.<\/li>\n<li><strong>Minimum and maximum revenue values<\/strong> among the filtered results.<\/li>\n<\/ul>\n<p>This approach minimizes network bandwidth usage and improves performance by pushing the aggregation logic to the server level.<\/p>\n<p>&#x1f4dd; <em>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.<\/em><\/p>\n<pre class=\"lang:css gutter:true start:1\">var query = new QueryExpression(\"account\") \/\/ Query on Account entity\r\n\r\n{\r\n\r\nColumnSet = new ColumnSet(false) \/\/ No regular fields, only aggregate expressions\r\n\r\n{\r\n\r\nAttributeExpressions = {\r\n\r\n{\r\n\r\nnew XrmAttributeExpression(attributeName : \"revenue\", alias : \"Average\", aggregateType : XrmAggregateType.Avg) }, \u00a0\/\/ Average revenue\r\n\r\n{\r\n\r\nnew XrmAttributeExpression(attributeName : \"revenue\", alias :\"Count\", aggregateType : XrmAggregateType.Count) }, \/\/ Count of records\r\n\r\n{ new XrmAttributeExpression(attributeName : \"revenue\", alias :\"Min\", aggregateType : XrmAggregateType.Min) }, \/\/ Minimum revenue\r\n\r\n{ new XrmAttributeExpression(attributeName : \"revenue\", alias :\"Max\", aggregateType : XrmAggregateType.Max) } \/\/ Maximum revenue\r\n\r\n}\r\n\r\n},\r\n\r\nCriteria = new FilterExpression(LogicalOperator.And) \/\/ Filtering conditions\r\n\r\n{\r\n\r\nConditions =\r\n\r\n{\r\n\r\nnew ConditionExpression(\"statecode\", ConditionOperator.Equal, 0), \/\/ Only active accounts\r\n\r\nnew ConditionExpression(\"ownerid\", ConditionOperator.Equal, new Guid(\"03b8f351-a246-f011-8779-7c1e520e8711\")) \/\/ Specific owner\r\n\r\n}\r\n\r\n}\r\n\r\n};\r\n\r\nvar results = service.RetrieveMultiple(query); \/\/ Execute the query\r\n\r\nif (results != null &amp;&amp; results.Entities != null &amp; results.Entities.Count &gt; 0)\r\n\r\n{\r\n\r\nEntity information = results.Entities[0];\r\n\r\nConsole.WriteLine(\"********************* Revenue Generated By USER 1 ********************\");\r\n\r\nConsole.WriteLine($\"********************* Average Revenue {((Money)((AliasedValue)information.Attributes[\"Average\"]).Value).Value} ********************\");\r\n\r\nConsole.WriteLine($\"********************* Count Revenue {((AliasedValue)information.Attributes[\"Count\"]).Value} ********************\");\r\n\r\nConsole.WriteLine($\"********************* Min Revenue {((Money)((AliasedValue)information.Attributes[\"Min\"]).Value).Value} ********************\");\r\n\r\nConsole.WriteLine($\"********************* Max Revenue {((Money)((AliasedValue)information.Attributes[\"Max\"]).Value).Value} ********************\");\r\n\r\n}\r\n\r\nelse\r\n\r\n{\r\n\r\nConsole.WriteLine(\"No Information Found\");\r\n\r\n}<\/pre>\n<p>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:<\/p>\n<h4><strong>Explanation<\/strong><\/h4>\n<ul>\n<li>Average Revenue: The mean value of all revenue fields from active account records owned by the specified user.<\/li>\n<li>Count Revenue: Total number of account records considered for aggregation.<\/li>\n<li>Min Revenue: The smallest revenue value among those records.<\/li>\n<li>Max Revenue: The largest revenue value among those records.<\/li>\n<\/ul>\n<h4><strong>Why It Matters<\/strong><\/h4>\n<p>Using QueryExpression for aggregation:<\/p>\n<ul>\n<li>Reduces round-trips to the server by returning only summarized results.<\/li>\n<li>Minimizes memory usage by avoiding the need to retrieve and process full datasets in code.<\/li>\n<li>Enhances performance for custom reporting tools, dashboards, or integration scenarios.<\/li>\n<\/ul>\n<h3><img decoding=\"async\" class=\"alignnone size-full wp-image-41569\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/1QueryExpression-in-Dynamics-365.png\" alt=\"QueryExpression in Dynamics 365\" width=\"1155\" height=\"151\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/1QueryExpression-in-Dynamics-365.png 1155w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/1QueryExpression-in-Dynamics-365-300x39.png 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/1QueryExpression-in-Dynamics-365-1024x134.png 1024w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/1QueryExpression-in-Dynamics-365-768x100.png 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/06\/1QueryExpression-in-Dynamics-365-660x86.png 660w\" sizes=\"(max-width: 1155px) 100vw, 1155px\" \/><\/h3>\n<h3><strong>Conclusion<\/strong><\/h3>\n<p>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\u2014such as the lack of support for DISTINCT counts, cross-entity aggregation, and large dataset boundaries.<\/p>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.inogic.com\/blog\/2025\/06\/using-aggregate-functions-with-queryexpression-in-dynamics-365\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16,2361],"tags":[3169],"class_list":["post-41568","post","type-post","status-publish","format-standard","hentry","category-dynamics-365","category-technical","tag-queryexpression-dynamics-365"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/41568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=41568"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/41568\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=41568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=41568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=41568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}