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

Sam Xu

January 11th, 202210 0

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:

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.
as is the keyword and must have one in one compute expression.
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.

Sam Xu Senior Software Engineer, OData, Microsoft Graph, Open API, Swagger

10 comments

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

Juliano Goncalves January 12, 2022 9:38 am 0

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 Xu January 12, 2022 11:15 am 0
  
  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.
Juliano Goncalves January 12, 2022 9:56 am 0

Another question that came up from a different article. On Adding support for $count segment in $filter collections in OData WebAPI, the following is mentioned:
According to the spec only $filter or $search query options can be applied to a $count segment..
Can you confirm if `$search` works inside a `$count` segment?
- Sam Xu January 12, 2022 11:18 am 0
  
  So far, no. It’s not supported in the current version. Do you mind filing an issue for us at GitHub repo?

Jeff Stockett January 12, 2022 10:00 am 0

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;
        }
    }
}

Sam Xu January 12, 2022 11:23 am 0

@Jeff Stockett Thanks for sharing your implementation. It’s great to see.
Moreover, if you want to support “NOT, OR, AND”, You can modify ‘Expression exp = BindSearchTerm((SearchTermNode)searchClause.Expression, context);’.
Just for your information, you can refer https://github.com/xuzhg/MyAspNetCore/blob/master/src/NewQueryOptionIn8/NewQueryOptionIn8/Extensions/ProductSaleSearchBinder.cs#L84-L91 for these supports.

Greg Hitchon January 13, 2022 2:27 pm 0

Wow! Thanks for putting together this post with so much detail…super helpful, love the new features!
Craig January 19, 2022 5:17 am 0

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?
- Sam Xu January 19, 2022 8:13 pm 0
  
  Would you please share more details about your requirement?
Felix L April 3, 2022 1:21 am 0

Is there a way to add new field to the search result? I would return a score field indicating the relevance of the result with the search term. Then the caller can use $oderby to sort by this score.

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

Introduction

Prerequisites

$compute query option

$compute syntax

$compute on sub property

$compute using built-in functions

Multiple compute expressions in $compute

$compute in $filter

$compute in $orderby

$compute as nested within $select and $expand:

$search query option

Implement $search binder

Register $search binder

Basic $search example

$search using NOT

$search using AND

$search using OR

$search using non-string token

$search with $filter

Summary

Sam Xu Senior Software Engineer, OData, Microsoft Graph, Open API, Swagger

Read next

10 comments