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.
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.
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?
Would you please share more details about your requirement?
Wow! Thanks for putting together this post with so much detail…super helpful, love the new features!
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...
@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.
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?
So far, no. It’s not supported in the current version. Do you mind filing an issue for us at GitHub repo?
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...
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...