{"id":3682,"date":"2020-04-24T13:21:06","date_gmt":"2020-04-24T20:21:06","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/odata\/?p=3682"},"modified":"2020-04-24T13:21:06","modified_gmt":"2020-04-24T20:21:06","slug":"aggregation-extensions-in-odata-asp-net-core","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/odata\/aggregation-extensions-in-odata-asp-net-core\/","title":{"rendered":"Aggregation extensions in OData ASP.NET Core"},"content":{"rendered":"

$select<\/strong> and $filter<\/strong>, as well as other OData query options, are an excellent way to receive only data that you need. However, they might not be the best option for reporting and analytical applications. If you want to get total sales to the particular customer and using only $select<\/strong> and $filter<\/strong>, you end up selecting all orders for that customer and doing aggregation client-side. This approach means sending a lot of data over the network. If you need to show sales by region, product category, you have to send almost all the data.<\/p>\n

Fortunately, OData v4.0 specification includes an aggregation extensions<\/a> which allows us to perform aggregations server-side and respond to a client with just a few numbers. It introduces a new query option $apply<\/strong>. We are going to show how to use it.<\/p>\n

Basic support for aggregation extensions was added into ASP.NET OData v7.0 and improved with each new version.<\/p>\n

This tutorial assumes that you already know how to build an ASP.NET Core Web Application service using the ASP.NET Core OData NuGet package. If not, start by reading ASP.NET Core OData now Available<\/a> and add Data Model and controllers as described below.<\/p>\n

You could use this sample project<\/a> to try all queries from this article.<\/p>\n

Let\u2019s get started. We will build an OData application that allows users to summarize sales data in the ad-hoc matter.<\/p>\n

Data Model<\/h2>\n

As mentioned, we aren’t going to build a project from scratch. Please, refer to ASP.NET Core OData now Available<\/a>, if you need detailed steps on how to create OData application.<\/h2>\n

As for data model we are going to use following CLR classes:<\/p>\n

\r\n\/\/ Entity types\r\npublic class Customer\r\n{\r\n    public int Id { get; set; }\r\n    public string Name { get; set; }\r\n    public IList Emails { get; set; }\r\n    public Address HomeAddress { get; set; }\r\n    public IList
 FavoriteAddresses { get; set; }\r\n    public Order PersonOrder { get; set; }\r\n    public IList Orders { get; set; }\r\n}\r\n\r\npublic class Order\r\n{\r\n    public int Id { get; set; }\r\n    public string Title { get; set; }\r\n    public decimal TotalAmount { get; set; }\r\n    public Customer Customer { get; set; }\r\n}\r\n\r\n\/\/ Complex types\r\npublic class Address\r\n{\r\n    public string Street { get; set }\r\n    public string City { get; se }\r\n    public ZipCode ZipCode { get; set }\r\n}\r\n\r\npublic class BillAddress : Address\r\n{\r\n    public string FirstName { get; set; }\r\n    public string LastName { get; set; }\r\n}\r\n\r\npublic class ZipCode\r\n{\r\n    public int Id { get; set; }\r\n    public string DisplayName { get; set; }\r\n}\r\n\r\n<\/pre>\n
Also, we are going to have two OData controllers: Customers and Orders<\/p>\n
\r\npublic class OrdersController : ODataController\r\n{\r\n    \/\/ Skipped\r\n\r\n    [EnableQuery]\r\n    public IEnumerable Get()\r\n    {\r\n        return _repository.GetOrders();\r\n    }\r\n}\r\npublic class CustomersController : ODataController\r\n{\r\n    \/\/ Skipped\r\n\r\n    [EnableQuery]\r\n    public IEnumerable Get()\r\n    {\r\n        return _repository.GetOrders();\r\n    }\r\n}\r\n\r\n<\/pre>\n
that will allow having two OData entity sets that we are going to query:\nhttp:\/\/localhost:5000\/odata\/Orders<\/strong> and http:\/\/localhost:5000\/odata\/Customers<\/strong><\/p>\n
Now we are ready to try a few aggregation queries. You don’t need to do anything specific to enable $apply<\/strong> query option.<\/p>\n
 $apply<\/h2>\n
$apply query option allows to specify a sequence of transformations to the entity set, such as groupby<\/strong>, filter<\/strong>, aggregate<\/strong>, etc. We will explain and demonstrate each later.<\/p>\n
aggregate transformation<\/h3>\n
Let’s start with a simple one and get the total number of orders<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=aggregate($count as OrderCount)\r\n<\/pre>\n
The query will collapse response into a single record and introduce new dynamic property OrderCount<\/strong> we will get the following output:<\/p>\n
\r\n{\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(OrderCount)\",\r\n    \"value\": [\r\n    {\r\n        \"@odata.id\": null,\r\n        \"OrderCount\": 3\r\n    }\r\n    ]\r\n}\r\n<\/pre>\n
This query might look not very impressive; you could get the number of orders without aggregation extensions by using http:\/\/localhost:5000\/odata\/Orders\/$count<\/strong>. In addition to $count we could use aggregation methods like sum<\/strong>, max<\/strong>, min<\/strong>, countdistinct<\/strong>, average<\/strong> and we could combine these aggregations into a single query. For example, the following query returns not only the number of orders but the total amount as well as average:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=aggregate($count as OrderCount, TotalAmount with sum as TotalAmount, TotalAmount with average as AverageAmount)\r\n<\/pre>\n
We will get the following output. We introduced 3 new properties with requested aggregations<\/p>\n
\r\n{\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(OrderCount,TotalAmount,AverageAmount)\",\r\n    \"value\": [\r\n        {\r\n            \"@odata.id\": null,\r\n            \"AverageAmount\": 22,\r\n            \"TotalAmount\": 66,\r\n            \"OrderCount\": 3\r\n        }\r\n    ]\r\n}\r\n<\/pre>\n
groupby transformation<\/h3>\n
We could get more complex results if we start using groupby<\/strong> transformation with or without nested aggregate.<\/p>\n
To get total orders by a customer we could use<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))\r\n\r\n<\/pre>\n
and get the following response:<\/p>\n
\r\n    {\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(Customer(Name),OrderCount,TotalAmount)\",\r\n    \"value\": [\r\n        {\r\n            \"@odata.id\": null,\r\n            \"TotalAmount\": 21,\r\n            \"OrderCount\": 1,\r\n            \"Customer\": {\r\n                \"@odata.id\": null,\r\n                \"Name\": \"Balmy\"\r\n            }\r\n        },\r\n        {\r\n            \"@odata.id\": null,\r\n            \"TotalAmount\": 45,\r\n            \"OrderCount\": 2,\r\n            \"Customer\": {\r\n                \"@odata.id\": null,\r\n                \"Name\": \"Chilly\"\r\n            }\r\n        }\r\n    ]\r\n}\r\n<\/pre>\n
Please, note that we are using Customer\/Name<\/strong> to access properties from related entities in the same way as we are doing it in $filter<\/strong> and getting properties from the Customer entity as nested JSON in the same way as we will get them while using $expand<\/strong><\/p>\n
Trick:<\/b> If we use groupby<\/strong> without aggregation we could get distinct customer names http:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name))<\/strong> or http:\/\/localhost:5000\/odata\/Customers?$apply=groupby((Name))<\/strong>. Please, note that syntax uses double parentheses.<\/p><\/blockquote>\n
filter transformation<\/h3>\n
We are using groupby<\/strong> and aggregate<\/strong> transformations; however, we use only one per query. $apply<\/strong> allows combining multiple transformations to get the desired output.<\/p>\n
We could adjust the previous query by getting orders only from customers in a particular city. To do that we first need to filter order using filter<\/strong> transformation filter(Customer\/HomeAddress\/City eq ‘Redonse’)<\/strong>, followed by the same groupby<\/strong> expression as in the previous query, \/<\/strong> used as the delimiter. The query will look like:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=filter(Customer\/HomeAddress\/City eq 'Redonse')\/groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))\r\n<\/pre>\n
We will get the following as output:<\/p>\n
\r\n    {\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(Customer(Name),OrderCount,TotalAmount)\",\r\n    \"value\": [\r\n        {\r\n            \"@odata.id\": null,\r\n            \"TotalAmount\": 21,\r\n            \"OrderCount\": 1,\r\n            \"Customer\": {\r\n                \"@odata.id\": null,\r\n                \"Name\": \"Balmy\"\r\n            }\r\n        }\r\n    ]\r\n}\r\n<\/pre>\n
Transformations will be executed from left to right. In the query above filter(…)<\/strong> will be executed first and then groupby(…)<\/strong> will be executed on already filtered data. Transformations could be combined in any order. It means that we could do the filtering of aggregated results. <\/p>\n
For example, if we are interested in finding customers that spent more that particular amount we could use groupby<\/strong> first and then filter<\/strong> results:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))\/filter(TotalAmount gt 23)   \r\n<\/pre>\n
\r\n    {\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(Customer(Name),OrderCount,TotalAmount)\",\r\n    \"value\": [\r\n        {\r\n            \"@odata.id\": null,\r\n            \"TotalAmount\": 45,\r\n            \"OrderCount\": 2,\r\n            \"Customer\": {\r\n                \"@odata.id\": null,\r\n                \"Name\": \"Chilly\"\r\n            }\r\n        }\r\n    ]\r\n}\r\n<\/pre>\n
It’s important always remember the order of transformation or we could get unexpected results. If we try to aggregate first and then try to filter by customers’ city:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))\/filter(Customer\/HomeAddress\/City eq 'Redonse')\r\n<\/pre>\n
we will get an error The query specified in the URI is not valid. $apply\/groupby grouping expression ‘City’ must evaluate to a property access value.<\/i><\/strong>.  It happens because after we applied groupby<\/strong> transformation we have access only to properties from groupby<\/strong> and aggregate<\/strong>.  <\/p>\n
$apply and other query options<\/h3>\n
$apply<\/strong> is yet another query option and can be combined with others such as $orderby<\/strong>, $filter<\/strong>, etc. It’s important to remember that $apply<\/strong> evaluated first<\/a>. It means that all dynamic properties introduced in the $apply<\/strong> will be available for later query options; however, properties that aren’t part of groupby<\/strong> or aggregate<\/strong> will be gone.<\/p>\n
To get customers ordered by the total amount you could use the following query:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as Total)&$orderby=Total desc\r\n<\/pre>\n
and get TOP N customers:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as Total)&$orderby=Total desc&$top=1\r\n<\/pre>\n
The result will look like<\/p>\n
\r\n{\r\n    \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(Customer(Name),OrderCount,Total)\",\r\n    \"value\": [\r\n        {\r\n            \"@odata.id\": null,\r\n            \"Total\": 45,\r\n            \"OrderCount\": 2,\r\n            \"Customer\": {\r\n                \"@odata.id\": null,\r\n                \"Name\": \"Chilly\"\r\n            }\r\n        }\r\n    ]\r\n}\r\n<\/pre>\n
\nTrick:<\/strong> If you are just looking for the biggest total amount, you could use additional aggregate<\/strong> after groupby<\/strong>:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate(TotalAmount with sum as Total))\/aggregate(Total with max as MaxTotal)\r\n<\/pre>\n
Output will look like:<\/p>\n
\r\n{\r\n   \"@odata.context\": \"http:\/\/localhost:5000\/odata\/$metadata#Orders(MaxTotal)\",\r\n   \"value\": [\r\n       {\r\n           \"@odata.id\": null,\r\n           \"MaxTotal\": 45\r\n       }\r\n   ]\r\n}\r\n<\/pre>\n<\/blockquote>\n
Using $filter<\/strong> after $apply<\/strong> is the same as final filter()<\/strong>transformation. Following 3 queries are equal:<\/p>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))\/filter(TotalAmount gt 23)   \r\n<\/pre>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))&$filter=TotalAmount gt 23\r\n<\/pre>\n
\r\nhttp:\/\/localhost:5000\/odata\/Orders?$filter=TotalAmount gt 23&$apply=groupby((Customer\/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))\r\n<\/pre>\n
$apply<\/strong> evaluated first no matter in which order it was specified in the query options<\/em><\/p>\n
Query providers<\/h2>\n
In this tutorial, we use Linq to objects, where all transformationshappen in memory. In real applications, you will use a more advanced query provider (concrete implementation of IQueryable) that will talk to some database and storage. Capabilities and performance of queries could be affected by the chosen query provider:<\/p>\n\n\n\n\n\n\n
Query provider<\/th>\n.NET Core or Classic<\/th>\nNotes<\/th>\n<\/tr>\n
EF6            <\/td>\n .NET Classic, .NET Core 3.0+ <\/td>\n Aggregation will be translated to SQL and executed as single SQL query <\/td>\n<\/tr>\n
EF Core 1.0    <\/td>\n .NET Classic, .NET Core <\/td>\n Aggregations not supported <\/td>\n<\/tr>\n
EF Core 2.1    <\/td>\n .NET Classic, .NET Core <\/td>\n Aggregation will be executed client side in memory <\/td>\n
<\/p>\n
EF Core 3.0\/3.1    <\/td>\n .NET Core 3.0+, .NET Classic (for EF Core 3.1) <\/td>\n Aggregations will be translated to SQL and executed as a single SQL query. However, not all expressions are supported <\/td>\n<\/tr>\n<\/table>\n
Stay tuned for the next blog post about using Entity Framework with $apply<\/strong>.<\/p>\n
Summary<\/h2>\n
$apply<\/strong> is compelling way to extend an OData endpoint and minimize the amount of data that transferred between a service and a client for reporting and analytical scenarios. You could use sample project<\/a> to play with queries from this article.<\/p>\n","protected":false},"excerpt":{"rendered":"
$select and $filter, as well as other OData query options, are an excellent way to receive only data that you need. However, they might not be the best option for reporting and analytical applications. If you want to get total sales to the particular customer and using only $select and $filter, you end up selecting […]<\/p>\n","protected":false},"author":18438,"featured_media":3253,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3682","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-odata"],"acf":[],"blog_post_summary":"
$select and $filter, as well as other OData query options, are an excellent way to receive only data that you need. However, they might not be the best option for reporting and analytical applications. If you want to get total sales to the particular customer and using only $select and $filter, you end up selecting […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/3682","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/users\/18438"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/comments?post=3682"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/3682\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media\/3253"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media?parent=3682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/categories?post=3682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/tags?post=3682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}