Aggregation extensions in OData ASP.NET Core

Konstantin Kosinsky

$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:

// 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 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:

http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as Total)&$orderby=Total desc

and get TOP N customers:

http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as Total)&$orderby=Total desc&$top=1

The result will look like

{
    "@odata.context": "http://localhost:5000/odata/$metadata#Orders(Customer(Name),OrderCount,Total)",
    "value": [
        {
            "@odata.id": null,
            "Total": 45,
            "OrderCount": 2,
            "Customer": {
                "@odata.id": null,
                "Name": "Chilly"
            }
        }
    ]
}

Trick: If you are just looking for the biggest total amount, you could use additional aggregate after groupby:

http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate(TotalAmount with sum as Total))/aggregate(Total with max as MaxTotal)

Output will look like:

{
   "@odata.context": "http://localhost:5000/odata/$metadata#Orders(MaxTotal)",
   "value": [
       {
           "@odata.id": null,
           "MaxTotal": 45
       }
   ]
}

Using $filter after $apply is the same as final filter()transformation. Following 3 queries are equal:

http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))/filter(TotalAmount gt 23)   

http://localhost:5000/odata/Orders?$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))&$filter=TotalAmount gt 23

http://localhost:5000/odata/Orders?$filter=TotalAmount gt 23&$apply=groupby((Customer/Name), aggregate($count as OrderCount, TotalAmount with sum as TotalAmount))

$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:

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.

Konstantin Kosinsky Principal Software Engineer, Azure DevOps

Follow