We will get an error The query specified in the URI is not valid. aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))/filter(Customer/HomeAddress/City eq 'Redonse') If we try to aggregate first and then try to filter by customers’ city:
It’s important always remember the order of transformation or we could get unexpected results. aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))/filter(TotalAmount gt " "value": null, It means that we could do the filtering of aggregated results.įor example, if we are interested in finding customers that spent more that particular amount we could use groupby first and then filter results: Transformations could be combined in any order. In the query above filter(…) will be executed first and then groupby(…) will be executed on already filtered data. Transformations will be executed from left to right. We will get the following as " "value": null, eq 'Redonse')/groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount)) 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. We could adjust the previous query by getting orders only from customers in a particular city. $apply allows combining multiple transformations to get the desired output. We are using groupby and aggregate transformations however, we use only one per query. Trick: If we use groupby without aggregation we could get distinct customer names or Please, note that syntax uses double parentheses. 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
aggregate($count as OrderCount, TotalAmount with sum as TotalAmount)) To get total orders by a customer we could use We could get more complex results if we start using groupby transformation with or without nested aggregate. We introduced 3 new properties with requested " "value": null, as OrderCount, TotalAmount with sum as TotalAmount, TotalAmount with average as AverageAmount) For example, the following query returns not only the number of orders but the total amount as well as average: This query might look not very impressive you could get the number of orders without aggregation extensions by using 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. The query will collapse response into a single record and introduce new dynamic property OrderCount we will get the following " "value": null, Let’s start with a simple one and get the total number of orders We will explain and demonstrate each later. $apply query option allows to specify a sequence of transformations to the entity set, such as groupby, filter, aggregate, etc. You don’t need to do anything specific to enable $apply query option. and Now we are ready to try a few aggregation queries. That will allow having two OData entity sets that we are going to query: Public class CustomersController : ODataController Public class OrdersController : ODataController Public IList FavoriteAddresses Īlso, we are going to have two OData controllers: Customers and Orders
#Microsoft expression web extensions how to
Please, refer to ASP.NET Core OData now Available, if you need detailed steps on how to create OData application.Īs for data model we are going to use following CLR classes:
Data ModelĪs mentioned, we aren’t going to build a project from scratch.
We will build an OData application that allows users to summarize sales data in the ad-hoc matter. You could use this sample project to try all queries from this article. If not, start by reading ASP.NET Core OData now Available and add Data Model and controllers as described below. 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. We are going to show how to use it.īasic support for aggregation extensions was added into ASP.NET OData v7.0 and improved with each new version. If you need to show sales by region, product category, you have to send almost all the data.įortunately, 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. This approach means sending a lot of data over the network. 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. However, they might not be the best option for reporting and analytical applications. $select and $filter, as well as other OData query options, are an excellent way to receive only data that you need.
0 kommentar(er)
