Aggregation extensions in OData ASP.NET Core

This post has been republished via RSS; it originally appeared at: Microsoft Developer Blogs.

$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 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. Fortunately, OData v4.0 specification includes an aggregation extensions 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. We are going to show how to use it. Basic support for aggregation extensions was added into ASP.NET OData v7.0 and improved with each new version. 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 and add Data Model and controllers as described below. You could use this sample project to try all queries from this article. Let’s get started. We will build an OData application that allows users to summarize sales data in the ad-hoc matter. Data Model As mentioned, we aren't going to build a project from scratch. Please, refer to ASP.NET Core OData now Available, if you need detailed steps on how to create OData application. As for data model we are going to use following CLR classes: Also, we are going to have two OData controllers: Customers and Orders that will allow having two OData entity sets that we are going to query: http://localhost:5000/odata/Orders and http://localhost:5000/odata/Customers Now we are ready to try a few aggregation queries. You don't need to do anything specific to enable $apply query option. $apply $apply query option allows to specify a sequence of transformations to the entity set, such as groupby, filter, aggregate, etc. We will explain and demonstrate each later. aggregate transformation Let's start with a simple one and get the total number of orders The query will collapse response into a single record and introduce new dynamic property OrderCount we will get the following output: 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. In addition to $count we could use aggregation methods like sum, max, min, countdistinct, average 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: We will get the following output. We introduced 3 new properties with requested aggregations groupby transformation We could get more complex results if we start using groupby transformation with or without nested aggregate. To get total orders by a customer we could use and get the following response: Please, note that we are using Customer/Name to access properties from related entities in the same way as we are doing it in $filter and getting properties from the Customer entity as nested JSON in the same way as we will get them while using $expand Trick: If we use groupby without aggregation we could get distinct customer names http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name)) or http://localhost:5000/odata/Customers?$apply=groupby((Name)). Please, note that syntax uses double parentheses. filter transformation We are using groupby and aggregate transformations; however, we use only one per query. $apply allows combining multiple transformations to get the desired output. 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 transformation filter(Customer/HomeAddress/City eq 'Redonse'), followed by the same groupby expression as in the previous query, / used as the delimiter. The query will look like: We will get the following as output: Transformations will be executed from left to right. In the query above filter(...) will be executed first and then groupby(...) 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. For example, if we are interested in finding customers that spent more that particular amount we could use groupby first and then filter results: 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: 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.. It happens because after we applied groupby transformation we have access only to properties from groupby and aggregate. $apply and other query options $apply is yet another query option and can be combined with others such as $orderby, $filter, etc. It's important to remember that $apply evaluated first. It means that all dynamic properties introduced in the $apply will be available for later query options; however, properties that aren't part of groupby or aggregate will be gone. To get customers ordered by the total amount you could use the following query: and get TOP N customers: The result will look like Trick: If you are just looking for the biggest total amount, you could use additional aggregate after groupby: Output will look like: Using $filter after $apply is the same as final filter()transformation. Following 3 queries are equal: $apply evaluated first no matter in which order it was specified in the query options Query providers 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: Query provider.NET Core or ClassicNotes EF6 .NET Classic, .NET Core 3.0+ Aggregation will be translated to SQL and executed as single SQL query EF Core 1.0 .NET Classic, .NET Core Aggregations not supported EF Core 2.1 .NET Classic, .NET Core Aggregation will be executed client side in memory EF Core 3.0/3.1 .NET Core 3.0+, .NET Classic (for EF Core 3.1) Aggregations will be translated to SQL and executed as a single SQL query. However, not all expressions are supported Stay tuned for the next blog post about using Entity Framework with $apply. Summary $apply 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 to play with queries from this article.