$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 and add Data Model and controllers as described below.
You could use this , if you need detailed steps on how to create OData application.
As for data model we are going to use following CLR classes:
// Entity types
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public IList Emails { get; set; }
public Address HomeAddress { get; set; }
public IList FavoriteAddresses { get; set; }
public Order PersonOrder { get; set; }
public IList Orders { get; set; }
}
public class Order
{
public int Id { get; set; }
public string Title { get; set; }
public decimal TotalAmount { get; set; }
public Customer Customer { get; set; }
}
// Complex types
public class Address
{
public string Street { get; set }
public string City { get; se }
public ZipCode ZipCode { get; set }
}
public class BillAddress : Address
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class ZipCode
{
public int Id { get; set; }
public string DisplayName { get; set; }
}
Also, we are going to have two OData controllers: Customers and Orders
public class OrdersController : ODataController
{
// Skipped
[EnableQuery]
public IEnumerable Get()
{
return _repository.GetOrders();
}
}
public class CustomersController : ODataController
{
// Skipped
[EnableQuery]
public IEnumerable Get()
{
return _repository.GetOrders();
}
}
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
http://localhost:5000/odata/Orders?$apply=aggregate($count as OrderCount)
The query will collapse response into a single record and introduce new dynamic property OrderCount we will get the following output:
{
"@odata.context": "http://localhost:5000/odata/$metadata#Orders(OrderCount)",
"value": [
{
"@odata.id": null,
"OrderCount": 3
}
]
}
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:
http://localhost:5000/odata/Orders?$apply=aggregate($count as OrderCount, TotalAmount with sum as TotalAmount, TotalAmount with average as AverageAmount)
We will get the following output. We introduced 3 new properties with requested aggregations
{
"@odata.context": "http://localhost:5000/odata/$metadata#Orders(OrderCount,TotalAmount,AverageAmount)",
"value": [
{
"@odata.id": null,
"AverageAmount": 22,
"TotalAmount": 66,
"OrderCount": 3
}
]
}
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
http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))
and get the following response:
{
"@odata.context": "http://localhost:5000/odata/$metadata#Orders(Customer(Name),OrderCount,TotalAmount)",
"value": [
{
"@odata.id": null,
"TotalAmount": 21,
"OrderCount": 1,
"Customer": {
"@odata.id": null,
"Name": "Balmy"
}
},
{
"@odata.id": null,
"TotalAmount": 45,
"OrderCount": 2,
"Customer": {
"@odata.id": null,
"Name": "Chilly"
}
}
]
}
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:
http://localhost:5000/odata/Orders?$apply=filter(Customer/HomeAddress/City eq 'Redonse')/groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))
We will get the following as output:
{
"@odata.context": "http://localhost:5000/odata/$metadata#Orders(Customer(Name),OrderCount,TotalAmount)",
"value": [
{
"@odata.id": null,
"TotalAmount": 21,
"OrderCount": 1,
"Customer": {
"@odata.id": null,
"Name": "Balmy"
}
}
]
}
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:
http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))/filter(TotalAmount gt 23)
{
"@odata.context": "http://localhost:5000/odata/$metadata#Orders(Customer(Name),OrderCount,TotalAmount)",
"value": [
{
"@odata.id": null,
"TotalAmount": 45,
"OrderCount": 2,
"Customer": {
"@odata.id": null,
"Name": "Chilly"
}
}
]
}
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:
http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))/filter(Customer/HomeAddress/City eq 'Redonse')
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 to play with queries from this article.
The post .
SOCIAL SHARE CARD GENERATOR