$compute and $search in ASP.NET Core OData 8

Sam

Introduction

OData system query options, such as $filter, $orderby, are a set of query string parameters that control the amount and order of the data returned for the resource identified by the URL. In the latest version of ASP.NET Core OData, two new system query options as follows are enabled:

  • $compute: allows clients to define computed properties that can be used in a $select or within a $filter or $orderby expression.
  • $search: allows clients to request items within a collection matching a free-text search expression.

Along with other query options, $compute or $search requests the service to perform a set of transformations at the server side to control the returned data. In this post, I will use a simple product-sale OData service to go through the $compute and $search scenarios and share ideas of how to use $compute and $search functionalities in ASP.NET Core OData.

Let’s get started.

Prerequisites

I created an ASP.NET Core web application called “NewQueryOptionIn8” using Visual Studio 2022 to play as product-sale OData service. I installed the following NuGet packages:

  • Microsoft.AspNetCore.OData -version 8.0.6

Product-sale and related classes are an in-memory model as follows:

Where:

  • Product & Sale: the entity types
  • Address: the complex type
  • Color & Category: the enum types
  • IProductSaleRepostiory & ProdcutSaleInMemoryRespository: the data source repository

You can refer to the Github repository here for detail class definitions, sample data, routing information, and the controllers.

$compute query option

$compute syntax

As mentioned, the $compute system query option allows clients to define computed properties that can be used in other query options. Basically, the computed properties should be included as dynamic properties in the result and must be included if $select is specified with the computed property name, or star (*). Here is an example:

GET http://localhost:5102/odata/products?$compute=Price mul Qty as TotalPrice&$select=TotalPrice

This request asks the server to compute the total price for products (using multiplication between Price and Qty) and return the total price only (included in $select) to the client. If you run the “NewQueryOptionIn8” application and send an HTTP Get request using the above URL, you should get the following response:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(TotalPrice)",
  "value": [
    {
      "TotalPrice": 65.0
    },
    {
      "TotalPrice": 59.85
    },
    {
      "TotalPrice": 149.5
    },
    {
      "TotalPrice": 900.0
    },
    {
      "TotalPrice": 97.86
    },
    {
      "TotalPrice": 1875.0
    }
  ]
}

Be noted, the remaining part after “=” in $compute=Price mul Qty as TotalPrice represents a compute expression. A compute expression used in $compute typically includes three parts:

  1. common expression: Price mul Qty is a common expression, where Price and Qty are properties, mul is the keyword for multiplication. You can refer to here for more detail information about common expression.
  2. as is the keyword and must have one in one compute expression.
  3. an alias name: TotalPrice is an alias name or the computed dynamic property name for common expression. It should follow up OData property naming rule and be unique.

$compute on sub property

Beside computing using direct property, you can use $compute on sub-property of complex type property. For example:

GET http://localhost:5102/odata/products?$compute=Location/ZipCode div 1000 as MainZipCode&$select=Id,MainZipCode

This request asks the server to compute the main zip code from Location of each product and return it with Id. The response should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Id,MainZipCode)",
  "value": [
    {
      "Id": 1,
      "MainZipCode": 58
    },
    {
      "Id": 2,
      "MainZipCode": 98
    },
    {
      "Id": 3,
      "MainZipCode": 13
    },
    {
      "Id": 4,
      "MainZipCode": 98
    },
    {
      "Id": 5,
      "MainZipCode": 58
    },
    {
      "Id": 6,
      "MainZipCode": 13
    }
  ]
}

$compute using built-in functions

More powerful, you can also use built-in functions in the $compute query option. For example:

GET http://localhost:5102/odata/products?$compute=substring(Name,0,1) as FirstChar&$select=FirstChar

This request asks the server to sub-string the first character from the product name and returns it. So, the response to the above request should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(FirstChar)",
  "value": [
    {
      "FirstChar": "B"
    },
    {
      "FirstChar": "P"
    },
    {
      "FirstChar": "N"
    },
    {
      "FirstChar": "F"
    },
    {
      "FirstChar": "P"
    },
    {
      "FirstChar": "V"
    }
  ]
}

Multiple compute expressions in $compute

$compute allows multiple compute expressions. Each expression uses comma (,) to separate. Here’s an example:

GET http://localhost:5102/odata/products?$compute=Price mul Qty as TotalPrice,TaxRate mul 1.1 as NewTaxRate&$select=Id,TotalPrice,NewTaxRate

This request asks the server to compute the total price for products, increase the tax rate by 10% and return the Id, total price, and the new tax rate together to the client. The response should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Id,TotalPrice,NewTaxRate)",
  "value": [
    {
      "Id": 1,
      "TotalPrice": 65.0,
      "NewTaxRate": 0.11000000000000001
    },
    {
      "Id": 2,
      "TotalPrice": 59.85,
      "NewTaxRate": 0.15400000000000003
    },
    {
      "Id": 3,
      "TotalPrice": 149.5,
      "NewTaxRate": 0.11000000000000001
    },
    {
      "Id": 4,
      "TotalPrice": 900.0,
      "NewTaxRate": 0.15400000000000003
    },
    {
      "Id": 5,
      "TotalPrice": 97.86,
      "NewTaxRate": 0.264
    },
    {
      "Id": 6,
      "TotalPrice": 1875.0,
      "NewTaxRate": 0.48400000000000004
    }
  ]
}

$compute in $filter

Besides be used in $select, the computed properties defined in $compute also can be used in a $filter expression. Here is an example combining $compute and $filter:

GET http://localhost:5102/odata/products?$filter=TotalPrice lt 100&$compute=Price mul Qty as TotalPrice

The request asks the server to compute the total price for product items, then return products whose total price is less than 100.

The response to the above request only returns the following three products (Id=1,2,5, omit for other properties).

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products",
  "value": [
    { "Id": 1, … },
    { "Id": 2, … },
    { "Id": 5, … }
  ]
}

$compute in $orderby

Same as $filter, the computed properties defined in $compute also can be used in a $orderby expression to do ordering. For example:

GET http://localhost:5102/odata/products?$orderby=TotalPrice&$compute=Price mul Qty as TotalPrice&$select=Id,TotalPrice

The response to this request should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Id,TotalPrice)",
  "value": [
    {
      "Id": 2,
      "TotalPrice": 59.85
    },
    {
      "Id": 1,
      "TotalPrice": 65.0
    },
    {
      "Id": 5,
      "TotalPrice": 97.86
    },
    {
      "Id": 3,
      "TotalPrice": 149.5
    },
    {
      "Id": 4,
      "TotalPrice": 900.0
    },
    {
      "Id": 6,
      "TotalPrice": 1875.0
    }
  ]
}

$compute as nested within $select and $expand:

From the above examples, we know that we can use the computed properties from $compute into $select. Moreover, $compute also can be used as a nested query option within $select and $expand. For example:

GET http://localhost:5102/odata/products?

$expand=Sales($filter=SaleYear eq 1999;$compute=year(SaleDate) as SaleYear)

&$select=Location($compute=ZipCode div 1000 as MainZipCode;$select=City,MainZipCode)

(Line breaks only for readability)

The response should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Id,Location/City,Location/MainZipCode,Sales())",
  "value": [
    {
      "Location": {
        "City": "Sammsh",
        "MainZipCode": 58
      },
      "Sales": []
    },
    {
      "Location": {
        "City": "Redd",
        "MainZipCode": 98
      },
      "Sales": []
    },
    {
      "Location": {
        "City": "Issah",
        "MainZipCode": 13
      },
      "Sales": [
        {
          "Id": 6,
          "SaleDate": "1999-08-12",
          "ProductId": 3,
          "Amount": 11
        }
      ]
    },
    {
      "Location": {
        "City": "Redd",
        "MainZipCode": 98
      },
      "Sales": []
    },
    {
      "Location": {
        "City": "Sammsh",
        "MainZipCode": 58
      },
      "Sales": []
    },
    {
      "Location": {
        "City": "Issah",
        "MainZipCode": 13
      },
      "Sales": [
        {
          "Id": 13,
          "SaleDate": "1999-06-23",
          "ProductId": 6,
          "Amount": 3
        }
      ]
    }
  ]
}

Where, only the sales whose sale year is equal to 1999 are expanded.

$search query option

As mentioned, the $search system query option restricts the result from the server to include only those items matching the specified, free-text search expression. Since the search expression is freestyle, the definition of what it means to match is dependent upon the customized implementation.

Implement $search binder

In the latest ASP.NET Core OData, we introduce the “ISearchBinder” interface that can empower developers to implement their own $search matching logics. Here is the interface definition:

public interface ISearchBinder
{
    Expression BindSearch(SearchClause searchClause, QueryBinderContext context);
}

Where:

  • SearchClause holds the search expression.
  • QueryBinderContext holds the information used in binding.

Basically, it is easy to implement a $search binder just by creating a new class that implements the ISearchBinder interface. For example:

public class YourSearchBinder : ISearchBinder
{
    public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
    { ...... }
}

Since I want to use the built-in QueryBinder binding functionalities, I derive ProductSaleSearchBinder class from QueryBinder as follows:

public class ProductSaleSearchBinder : QueryBinder, ISearchBinder
{
    public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
    { ...... }
}

In ProductSaleSearchBinder, I use two ways to return an Expression based on SearchClause for your reference.

1) Lambda Func: Directly using a Lambda Func to generate an Expression. For example (Codes are simplified for readability):

public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
{
    SearchTermNode node = searchClause.Expression as SearchTermNode;

    Expression<Func<Product, bool>> exp = p => p.Category.ToString() == node.Text;

    return exp;
}

2) Expression Tree API: Creating Expression tree using the Expression APIs, for example:

public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
{
    SearchTermNode node = searchClause.Expression as SearchTermNode;

    Expression source = context.CurrentParameter;

    Expression categoryProperty = Expression.Property(source, "Category");

    Expression categoryPropertyString = Expression.Call(categoryProperty, "ToString", typeArguments: null, arguments: null);

    Expression body = Expression.Call(null, StringEqualsMethodInfo, categoryPropertyString, Expression.Constant(node.Text, typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase, typeof(StringComparison)));

    LambdaExpression lambdaExp = Expression.Lambda(body, context.CurrentParameter);

    return lambdaExp;
}

Lambda Func Expression is more readable and understandable compared to Expression tree API. However, if you want to build more complicated expressions, Expression tree API could be a better choice.

For the final ProductSaleSearchBinder implementation, please refer to Github repo here.

Register $search binder

It is easy to register the $search binder into service provider just updating the Program.cs as follows:

builder.Services.AddControllers()
    .AddOData(opt => opt.EnableQueryFeatures()
    .AddRouteComponents("odata", ModelBuilder.GetEdmModel(), services => services.AddSingleton<ISearchBinder, ProductSaleSearchBinder>()));

Basic $search example

Now we can filter products using $search. For example:

GET http://localhost:5102/odata/products?$search=food&$select=Name

The response contains food product name should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Name)",
  "value": [
    {
      "Name": "Bread"
    },
    {
      "Name": "Noodle"
    }
  ]
}

$search using NOT

We can use “NOT” keyword to reverse the search. For example:

GET http://localhost:5102/odata/products?$search=NOT food&$select=Name

The response contains non-food product name should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Name)",
  "value": [
    {
      "Name": "Pencil"
    },
    {
      "Name": "Flute"
    },
    {
      "Name": "Paper"
    },
    {
      "Name": "Violin"
    }
  ]
}

$search using AND

We can use “AND” to narrow the search. For example:

GET http://localhost:5102/odata/products?$search=food AND white&$select=Id,Name

The response should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Id,Name)",
  "value": [
    {
      "Id": 1,
      "Name": "Bread"
    }
  ]
}

$search using OR

Also, we can use “OR” to combine the search. For example:

GET http://localhost:5102/odata/products?$search=food OR White &$select=Name,Color,Category

Here’s the result:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Products(Name,Color,Category)",
  "value": [
    {
      "Name": "Bread",
      "Category": "Food",
      "Color": "White"
    },
    {
      "Name": "Noodle",
      "Category": "Food",
      "Color": "Brown"
    },
    {
      "Name": "Paper",
      "Category": "Office",
      "Color": "White"
    }
  ]
}

$search using non-string token

If the $search token contains non-string character, we can use double quote (“…”) to wrapper the search token. For example:

GET http://localhost:5102/odata/sales?$search=”2022″ OR “2018”&$select=SaleDate

The response should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Sales(SaleDate)",
  "value": [
    {
      "SaleDate": "2018-03-19"
    },
    {
      "SaleDate": "2022-12-09"
    },
    {
      "SaleDate": "2022-05-29"
    },
    {
      "SaleDate": "2022-01-01"
    }
  ]
}

$search with $filter

We can combine the $filter and $search together. The results include only those items that match both criteria. For example:

GET http://localhost:5102/odata/sales?$search=”2022″ OR “2018”&$select=SaleDate,Id&$filter=Id gt 8

The response should look like:

{
  "@odata.context": "http://localhost:5102/odata/$metadata#Sales(SaleDate,Id)",
  "value": [
    {
      "Id": 9,
      "SaleDate": "2022-05-29"
    },
    {
      "Id": 11,
      "SaleDate": "2022-01-01"
    }
  ]
}

That’s all.

Summary

This post went through $compute and $search query functionalities introduced in the latest ASP.NET Core OData 8 using lots of examples. Hope the contents and implementations in this post can help you easily apply $compute and $search in your service. Please do not hesitate to leave your comments below or let me know your thoughts through saxu@microsoft.com. Thanks.

I uploaded the whole project to this repository.

9 comments

Leave a comment

  • Juliano Goncalves

    This is absolutely great to see. Both query options are extremely useful and have been missing for so long!

    I got a few questions to you. First one is regarding the search implementation. What if one want’s to have different search implementations for different entity sets? The fact that it is registered in the container gives me the impression that only one implementation of the interface is supported, however it would be nice if one could define search to have different implementations for each entity set for example (say, for simpler scenarios where one can hardcode the checks against specific properties in the model). Is there any way to achieve this result in a straightforward manner?

    Second question is in regards to OData metadata. Is there any way to surface information about the search implementation as part of the metadata documentation? Since the implementation is free for the server to decide (contrary to other, more strict query options), it would be great if there was a clean way of communicating how a given API’s search function works so that consumers know how to use it, without having to maintain separate, dedicated docs for that. Does OData metadata support such “free-form comments” functionality and would it make sense to use it for documenting how search works?

    Third, about ‘compute’, is there a particular reason why the computed properties are not included automatically in the query and have to instead be explicitly selected? For instance, on this request `http://localhost:5102/odata/products?$compute=substring(Name,0,1) as FirstChar&$select=FirstChar`, why require one to explicitly pass in the `$select=FirstChar` part? If one adds a `compute` option without a `select` option, the whole computation is basically useless. Wouldn’t it make sense to add any aliased property to the output automatically if a `select` is not specified? A query such as `http://localhost:5102/odata/products?$compute=substring(Name,0,1) as FirstChar` would then return every product property as usual, plus the added `FirstChar` property. Is this somehow limited by the OData spec, or is it just an implementation detail on the .NET implementation that forces the need of the select?

    Lastly still on `compute`. Is it possible to leverage aliases created in the compute query itself on subsequent further computation? For example, using the previous query, would this work as expected? `http://localhost:5102/odata/products?$compute=substring(Name,0,1) as FirstChar, length(Name) as NameLength, concat(FirstChar,NameLength) as Code&$select=Code`. Note we create 2 aliases, and then use them to create a third alias. If this is not supported as-is, is it because of OData spec limitations, or could it be added? Also, are there any workarounds to achieve the same results using some other approach?

    • Sam XuMicrosoft employee

      Thanks for your questions.
      #1. Absolutely, it supports have different search implementation for different entity set. In my “NewQueryOptionIn8” application, I have already implemented different implement for different entity set (Products and Sale). Meanwhile, I also implemented two different ways (Lambda Func & LINQ Expression Tree) for all.

      #2. If you are looking for the capabilities for $metadata, yes, OData has the SearchRestriction capabilities defined https://github.com/oasis-tcs/odata-vocabularies/blob/main/vocabularies/Org.OData.Capabilities.V1.xml#L552. If that doesn’t meet your requirement, you can create your own term to embedded it into the metadata.

      #3. OData specs says “Computed properties SHOULD be included as dynamic properties in the result and MUST be included if $select is specified with the computed property name, or star (*).” It’s related to how to understand “SHOULD” and “MUST”. So far, my implementation follows up “MUST be included if $select is specified”. I’d like to sync with architect to re-think it again.

  • Jeff Stockett

    Yes – thank you so much for modular $search – we had been having to keep our own “hacked” version of odata in our tree to implement $search how we wanted, but reimplementing it using the new ISearchBinder interface was easy and now we can just use the stock library. Here is our implementation which works well using efcore6 and npgsql with citext for string columns. Note that this implementation requires you do $search=”whatever the user types in the search box” to keep the SearchClause a simple node rather than the more elaborate boolean logic mentioned in the post – our users would never be able to grok that.

    using Microsoft.AspNetCore.OData.Query.Expressions;
    using Microsoft.OData.Edm;
    using Microsoft.OData.UriParser;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Text.RegularExpressions;
    
    namespace MolallaFM.WebApi
    {
        public class CustomSearchBinder : ISearchBinder
        {
            //internal static readonly MethodInfo StringContainsMethodInfo = typeof(string).GetMethod("Contains", new[] { typeof(string), typeof(StringComparison) });
            internal static readonly MethodInfo StringContainsMethodInfo = typeof(string).GetMethod("Contains", new[] { typeof(string) });
            internal static readonly Regex NumExpr = new(@"^\d+$");
    
            public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)
            {
                Expression exp = BindSearchTerm((SearchTermNode)searchClause.Expression, context);
    
                LambdaExpression lambdaExp = Expression.Lambda(exp, context.CurrentParameter);
    
                return lambdaExp;
            }
    
            public Expression BindSearchTerm(SearchTermNode node, QueryBinderContext context)
            {
                List exps = new(); string text = node.Text; Expression combined = null;
    
                var t = (Microsoft.OData.Edm.EdmEntityType)context.Model.FindDeclaredType(context.ElementType.FullTypeName());
    
                if (NumExpr.Match(text).Success && t.DeclaredKey.First().Type.FullName() == "Edm.Int32")   // search text is just a number - lookup by PK if PK is an int or AccountID if that property exists and is an int
                {
                    int val = int.Parse(text);
                    exps.Add(Expression.Equal(Expression.Property(context.CurrentParameter, t.DeclaredKey.First().Name), Expression.Constant(val, typeof(int))));
    
                    var accountid = t.DeclaredProperties.FirstOrDefault(p => p.Name == "AccountID" && p.Name != t.DeclaredKey.First().Name && p.Type.FullName() == "Edm.Int32");
    
                    if (accountid != null)
                    {
                        if (accountid.Type.IsNullable)
                            exps.Add(Expression.Equal(Expression.Property(context.CurrentParameter, "AccountID"), Expression.Constant(val, typeof(int?))));
                        else
                            exps.Add(Expression.Equal(Expression.Property(context.CurrentParameter, "AccountID"), Expression.Constant(val, typeof(int))));
                    }
                }
    
                foreach (var prop in t.DeclaredProperties)
                {
                    if (prop.Type.FullName() == "Edm.String")
                    {
                        var notnull = Expression.NotEqual(Expression.Property(context.CurrentParameter, prop.Name), Expression.Constant(null, typeof(string)));
                        var contains = Expression.Call(Expression.Property(context.CurrentParameter, prop.Name), StringContainsMethodInfo, Expression.Constant(text, typeof(string)));
    
                        exps.Add(Expression.AndAlso(notnull, contains));
                    }
                }
    
                if (exps.Count > 0)
                    combined = exps.Skip(1).Aggregate(exps.FirstOrDefault(), (exp1, exp2) => Expression.Or(exp1, exp2));
                else
                    combined = Expression.Constant(false, typeof(bool));
    
                return combined;
            }
        }
    }
  • Craig

    Do you think the $search functionality could realistically allow you to do searching in a polyglot persistence model? e.g. The core metadata is in MS SQLServer (EF) and the $search actually filtered based on an elastic search index?