Aggregation extensions in OData ASP.NET Core

Konstantin Kosinsky

Konstantin

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

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.

3 comments

Comments are closed. Login to edit/delete your existing comments

  • Avatar
    Cecil Phillip

    I appreciate these samples that use in-memory data, but for a feature like this it would be great to see the experience using data stores via Cosmos SDK or EF Core. Also, is it possible to use async action methods with OData? I can imagine returning an IQueryable versus a Task result in a blocking call on the query.

  • Erick Fuentes
    Erick Fuentes

    Hello,

    I’ve been recently using OData in my dotnet core api and it’s great. The third part library I use in the front end (for Blazor) has widgets with OData queries. However, it relies on $search parameter. I have noticed that it is not implemented in the Microsoft OData Nuget package. Is this going to be solved anytime soon?

    Regards,

    Erick