- \n
- $compute<\/strong><\/a>: allows clients to define computed properties that can be used in a\u00a0$select<\/a>\u00a0or within a\u00a0$filter\u00a0or\u00a0$orderby\u00a0expression.<\/li>\n
- $search<\/strong><\/a>: allows clients to request items within a collection matching a free-text\u00a0search expression<\/a>.<\/li>\n<\/ul>\n
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<\/a>.<\/p>\n
Let’s get started.<\/p>\n
Prerequisites<\/h2>\n
I created an ASP.NET Core web application called \u201cNewQueryOptionIn8<\/strong>\u201d using Visual Studio 2022 to play as product-sale OData service. I installed the following NuGet packages:<\/p>\n
- \n
- Microsoft.AspNetCore.OData -version 8.0.6<\/li>\n<\/ul>\n
Product-sale and related classes are an in-memory model as follows:<\/p>\n
![](\"https:\/\/devblogs.microsoft.com\/odata\/wp-content\/uploads\/sites\/23\/2022\/01\/word-image.png\")
<\/p>\nWhere:<\/p>\n
- \n
- Product<\/strong> & Sale<\/strong>: the entity types<\/li>\n
- Address<\/strong>: the complex type<\/li>\n
- Color<\/strong> & Category<\/strong>: the enum types<\/li>\n
- IProductSaleRepostiory<\/strong> & ProdcutSaleInMemoryRespository<\/strong>: the data source repository<\/li>\n<\/ul>\n
You can refer to the Github repository here<\/a> for detail class definitions, sample data, routing information, and the controllers.<\/p>\n
$compute query option<\/h2>\n
$compute syntax<\/h2>\n
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\u00a0$select\u00a0is specified with the computed property name, or star (*). Here is an example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$compute=Price mul Qty as TotalPrice&$select=TotalPrice<\/em><\/strong><\/p>\n
This request asks the server to compute the total price for products (using multiplication between Price<\/em><\/strong> and Qty<\/em><\/strong>) and return the total price only (included in $select) to the client. If you run the \u201cNewQueryOptionIn8<\/strong>\u201d application and send an HTTP Get request using the above URL, you should get the following response:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(TotalPrice)\",\r\n \"value\":\u00a0[\r\n {\r\n \"TotalPrice\":\u00a065.0\r\n },\r\n {\r\n \"TotalPrice\":\u00a059.85\r\n },\r\n {\r\n \"TotalPrice\":\u00a0149.5\r\n },\r\n {\r\n \"TotalPrice\":\u00a0900.0\r\n },\r\n {\r\n \"TotalPrice\":\u00a097.86\r\n },\r\n {\r\n \"TotalPrice\":\u00a01875.0\r\n }\r\n ]\r\n}\r\n<\/pre>\nBe noted, the remaining part after \u201c=\u201d in $compute=Price mul Qty as TotalPrice<\/em> <\/strong>represents a compute expression. A compute expression used in $compute typically includes three parts:<\/p>\n
- \n
- common expression: Price mul Qty<\/em><\/strong> is a common expression, where Price<\/em><\/strong> and Qty<\/em><\/strong> are properties, mul<\/em><\/strong> is the keyword for multiplication. You can refer to here<\/a> for more detail information about common expression.<\/li>\n
- as<\/em><\/strong> is the keyword and must have one in one compute expression.<\/li>\n
- an alias name: TotalPrice<\/em><\/strong> is an alias name or the computed dynamic property name for common expression. It should follow up OData property naming rule and be unique.<\/li>\n<\/ol>\n
$compute on sub property<\/h2>\n
Beside computing using direct property, you can use $compute on sub-property of complex type property. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$compute=Location\/ZipCode div 1000 as MainZipCode&$select=Id,MainZipCode<\/em><\/strong><\/p>\n
This request asks the server to compute the main zip code from Location<\/em><\/strong> of each product and return it with Id. The response should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Id,MainZipCode)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Id\":\u00a01,\r\n \"MainZipCode\":\u00a058\r\n },\r\n {\r\n \"Id\":\u00a02,\r\n \"MainZipCode\":\u00a098\r\n },\r\n {\r\n \"Id\":\u00a03,\r\n \"MainZipCode\":\u00a013\r\n },\r\n {\r\n \"Id\":\u00a04,\r\n \"MainZipCode\":\u00a098\r\n },\r\n {\r\n \"Id\":\u00a05,\r\n \"MainZipCode\":\u00a058\r\n },\r\n {\r\n \"Id\":\u00a06,\r\n \"MainZipCode\":\u00a013\r\n }\r\n ]\r\n}\r\n<\/pre>\n$compute using built-in functions<\/h2>\n
More powerful, you can also use built-in functions<\/a> in the $compute query option. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$compute=substring(Name,0,1) as FirstChar&$select=FirstChar<\/em><\/strong><\/p>\n
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:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(FirstChar)\",\r\n \"value\":\u00a0[\r\n {\r\n \"FirstChar\":\u00a0\"B\"\r\n },\r\n {\r\n \"FirstChar\":\u00a0\"P\"\r\n },\r\n {\r\n \"FirstChar\":\u00a0\"N\"\r\n },\r\n {\r\n \"FirstChar\":\u00a0\"F\"\r\n },\r\n {\r\n \"FirstChar\":\u00a0\"P\"\r\n },\r\n {\r\n \"FirstChar\":\u00a0\"V\"\r\n }\r\n ]\r\n}\r\n<\/pre>\nMultiple compute expressions in $compute<\/h2>\n
$compute allows multiple compute expressions. Each expression uses comma (,) to separate. Here\u2019s an example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$compute=Price mul Qty as TotalPrice,TaxRate mul 1.1 as NewTaxRate&$select=Id,TotalPrice,NewTaxRate<\/em><\/strong><\/p>\n
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:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Id,TotalPrice,NewTaxRate)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Id\":\u00a01,\r\n \"TotalPrice\":\u00a065.0,\r\n \"NewTaxRate\":\u00a00.11000000000000001\r\n },\r\n {\r\n \"Id\":\u00a02,\r\n \"TotalPrice\":\u00a059.85,\r\n \"NewTaxRate\":\u00a00.15400000000000003\r\n },\r\n {\r\n \"Id\":\u00a03,\r\n \"TotalPrice\":\u00a0149.5,\r\n \"NewTaxRate\":\u00a00.11000000000000001\r\n },\r\n {\r\n \"Id\":\u00a04,\r\n \"TotalPrice\":\u00a0900.0,\r\n \"NewTaxRate\":\u00a00.15400000000000003\r\n },\r\n {\r\n \"Id\":\u00a05,\r\n \"TotalPrice\":\u00a097.86,\r\n \"NewTaxRate\":\u00a00.264\r\n },\r\n {\r\n \"Id\":\u00a06,\r\n \"TotalPrice\":\u00a01875.0,\r\n \"NewTaxRate\":\u00a00.48400000000000004\r\n }\r\n ]\r\n}\r\n<\/pre>\n$compute in $filter<\/h2>\n
Besides be used in $select, the computed properties defined in $compute also can be used in a\u00a0$filter\u00a0expression. Here is an example combining $compute and $filter:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$filter=TotalPrice lt 100&$compute=Price mul Qty as TotalPrice<\/em><\/strong><\/p>\n
The request asks the server to compute the total price for product items, then return products whose total price is less than 100.<\/p>\n
The response to the above request only returns the following three products (Id=1,2,5, omit for other properties).<\/p>\n
{\r\n \"@odata.context\": \"http:\/\/localhost:5102\/odata\/$metadata#Products\",\r\n \"value\": [\r\n { \"Id\": 1, \u2026 },\r\n { \"Id\": 2, \u2026 },\r\n { \"Id\": 5, \u2026 }\r\n ]\r\n}\r\n<\/pre>\n$compute in $orderby<\/h2>\n
Same as $filter, the computed properties defined in $compute also can be used in a\u00a0$orderby\u00a0expression to do ordering. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$orderby=TotalPrice&$compute=Price mul Qty as TotalPrice&$select=Id,TotalPrice<\/em><\/strong><\/p>\n
The response to this request should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Id,TotalPrice)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Id\":\u00a02,\r\n \"TotalPrice\":\u00a059.85\r\n },\r\n {\r\n \"Id\":\u00a01,\r\n \"TotalPrice\":\u00a065.0\r\n },\r\n {\r\n \"Id\":\u00a05,\r\n \"TotalPrice\":\u00a097.86\r\n },\r\n {\r\n \"Id\":\u00a03,\r\n \"TotalPrice\":\u00a0149.5\r\n },\r\n {\r\n \"Id\":\u00a04,\r\n \"TotalPrice\":\u00a0900.0\r\n },\r\n {\r\n \"Id\":\u00a06,\r\n \"TotalPrice\":\u00a01875.0\r\n }\r\n ]\r\n}\r\n<\/pre>\n$compute as nested within $select and $expand:<\/h2>\n
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:<\/p>\n
GET <\/strong>http:\/\/localhost:5102\/odata\/products<\/em><\/strong><\/a>?<\/em><\/strong><\/p>\n
$expand=Sales($filter=SaleYear eq 1999;$compute=year(SaleDate) as SaleYear)<\/em><\/strong><\/p>\n
&$select=Location($compute=ZipCode div 1000 as MainZipCode;$select=City,MainZipCode)<\/em><\/strong><\/p>\n
(Line breaks only for readability)<\/em><\/p>\n
The response should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Id,Location\/City,Location\/MainZipCode,Sales())\",\r\n \"value\":\u00a0[\r\n {\r\n \"Location\":\u00a0{\r\n \"City\":\u00a0\"Sammsh\",\r\n \"MainZipCode\":\u00a058\r\n },\r\n \"Sales\":\u00a0[]\r\n },\r\n {\r\n \"Location\":\u00a0{\r\n \"City\":\u00a0\"Redd\",\r\n \"MainZipCode\":\u00a098\r\n },\r\n \"Sales\":\u00a0[]\r\n },\r\n {\r\n \"Location\":\u00a0{\r\n \"City\":\u00a0\"Issah\",\r\n \"MainZipCode\":\u00a013\r\n },\r\n \"Sales\":\u00a0[\r\n {\r\n \"Id\":\u00a06,\r\n \"SaleDate\":\u00a0\"1999-08-12\",\r\n \"ProductId\":\u00a03,\r\n \"Amount\":\u00a011\r\n }\r\n ]\r\n },\r\n {\r\n \"Location\":\u00a0{\r\n \"City\":\u00a0\"Redd\",\r\n \"MainZipCode\":\u00a098\r\n },\r\n \"Sales\":\u00a0[]\r\n },\r\n {\r\n \"Location\":\u00a0{\r\n \"City\":\u00a0\"Sammsh\",\r\n \"MainZipCode\":\u00a058\r\n },\r\n \"Sales\":\u00a0[]\r\n },\r\n {\r\n \"Location\":\u00a0{\r\n \"City\":\u00a0\"Issah\",\r\n \"MainZipCode\":\u00a013\r\n },\r\n \"Sales\":\u00a0[\r\n {\r\n \"Id\":\u00a013,\r\n \"SaleDate\":\u00a0\"1999-06-23\",\r\n \"ProductId\":\u00a06,\r\n \"Amount\":\u00a03\r\n }\r\n ]\r\n }\r\n ]\r\n}\r\n<\/pre>\nWhere, only the sales whose sale year is equal to 1999 are expanded.<\/p>\n
$search query option<\/h2>\n
As mentioned, the\u00a0$search\u00a0system query option restricts the result from the server to include only those items\u00a0matching\u00a0the specified, free-text\u00a0search expression. Since the search expression is freestyle, the definition of what it means to match is dependent upon the customized implementation.<\/p>\n
Implement $search binder<\/h2>\n
In the latest ASP.NET Core OData, we introduce the \u201cISearchBinder<\/strong>\u201d interface that can empower developers to implement their own $search matching logics. Here is the interface definition:<\/p>\n
public interface ISearchBinder\r\n{\r\n Expression BindSearch(SearchClause searchClause, QueryBinderContext context);\r\n}\r\n<\/pre>\nWhere:<\/p>\n
- \n
- SearchClause<\/strong> holds the search expression.<\/li>\n
- QueryBinderContext <\/strong>holds the information used in binding.<\/li>\n<\/ul>\n
Basically, it is easy to implement a $search binder just by creating a new class that implements the ISearchBinder<\/strong> interface. For example:<\/p>\n
public class YourSearchBinder : ISearchBinder\r\n{\r\n public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)\r\n { ...... }\r\n}\r\n<\/pre>\nSince I want to use the built-in QueryBinder<\/strong> binding functionalities, I derive ProductSaleSearchBinder<\/strong> class from QueryBinder<\/strong> as follows:<\/p>\n
public class ProductSaleSearchBinder : QueryBinder, ISearchBinder\r\n{\r\n public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)\r\n { ...... }\r\n}\r\n<\/pre>\nIn ProductSaleSearchBinder<\/strong>, I use two ways to return an Expression based on SearchClause<\/strong> for your reference.<\/p>\n
1) Lambda Func<\/strong>: Directly using a Lambda Func to generate an Expression. For example (Codes are simplified for readability):<\/p>\n
public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)\r\n{\r\n SearchTermNode node = searchClause.Expression as SearchTermNode;\r\n\r\n Expression<Func<Product, bool>> exp = p => p.Category.ToString() == node.Text;\r\n\r\n return exp;\r\n}\r\n<\/pre>\n2) Expression Tree API<\/strong>: Creating Expression tree using the Expression APIs, for example:<\/p>\n
public Expression BindSearch(SearchClause searchClause, QueryBinderContext context)\r\n{\r\n SearchTermNode node = searchClause.Expression as SearchTermNode;\r\n\r\n Expression source = context.CurrentParameter;\r\n\r\n Expression categoryProperty = Expression.Property(source, \"Category\");\r\n\r\n Expression categoryPropertyString = Expression.Call(categoryProperty, \"ToString\", typeArguments: null, arguments: null);\r\n\r\n Expression body = Expression.Call(null, StringEqualsMethodInfo, categoryPropertyString, Expression.Constant(node.Text, typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase, typeof(StringComparison)));\r\n\r\n LambdaExpression lambdaExp = Expression.Lambda(body, context.CurrentParameter);\r\n\r\n return lambdaExp;\r\n}\r\n<\/pre>\nLambda 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.<\/p>\n
For the final ProductSaleSearchBinder<\/strong> implementation, please refer to Github repo here<\/a>.<\/p>\n
Register $search binder<\/h2>\n
It is easy to register the $search binder into service provider just updating the Program.cs<\/strong> as follows:<\/p>\n
builder.Services.AddControllers()\r\n .AddOData(opt => opt.EnableQueryFeatures()\r\n .AddRouteComponents(\"odata\", ModelBuilder.GetEdmModel(), services => services.AddSingleton<ISearchBinder, ProductSaleSearchBinder>()));\r\n<\/pre>\n
Basic $search example<\/h2>\n
Now we can filter products using $search. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$search=food&$select=Name<\/em><\/strong><\/a><\/p>\n
The response contains food product name should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Name)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Name\":\u00a0\"Bread\"\r\n },\r\n {\r\n \"Name\":\u00a0\"Noodle\"\r\n }\r\n ]\r\n}\r\n<\/pre>\n$search using NOT<\/strong><\/h2>\n
We can use \u201cNOT<\/em><\/strong>\u201d keyword to reverse the search. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$search=NOT food&$select=Name<\/em><\/strong><\/p>\n
The response contains non-food product name should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Name)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Name\":\u00a0\"Pencil\"\r\n },\r\n {\r\n \"Name\":\u00a0\"Flute\"\r\n },\r\n {\r\n \"Name\":\u00a0\"Paper\"\r\n },\r\n {\r\n \"Name\":\u00a0\"Violin\"\r\n }\r\n ]\r\n}\r\n<\/pre>\n$search using AND<\/strong><\/h2>\n
We can use \u201cAND<\/em><\/strong>\u201d to narrow the search. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$search=food AND white&$select=Id,Name<\/em><\/strong><\/p>\n
The response should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Id,Name)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Id\":\u00a01,\r\n \"Name\":\u00a0\"Bread\"\r\n }\r\n ]\r\n}\r\n<\/pre>\n$search using OR<\/strong><\/h2>\n
Also, we can use \u201cOR<\/em><\/strong>\u201d to combine the search. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/products?$search=food OR White &$select=Name,Color,Category<\/em><\/strong><\/p>\n
Here\u2019s the result:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Products(Name,Color,Category)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Name\":\u00a0\"Bread\",\r\n \"Category\":\u00a0\"Food\",\r\n \"Color\":\u00a0\"White\"\r\n },\r\n {\r\n \"Name\":\u00a0\"Noodle\",\r\n \"Category\":\u00a0\"Food\",\r\n \"Color\":\u00a0\"Brown\"\r\n },\r\n {\r\n \"Name\":\u00a0\"Paper\",\r\n \"Category\":\u00a0\"Office\",\r\n \"Color\":\u00a0\"White\"\r\n }\r\n ]\r\n}\r\n<\/pre>\n$search using non-string token<\/h2>\n
If the $search token contains non-string character, we can use double quote (\u201c\u2026\u201d) to wrapper the search token. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/sales?$search=”2022″ OR “2018”&$select=SaleDate<\/em><\/strong><\/p>\n
The response should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Sales(SaleDate)\",\r\n \"value\":\u00a0[\r\n {\r\n \"SaleDate\":\u00a0\"2018-03-19\"\r\n },\r\n {\r\n \"SaleDate\":\u00a0\"2022-12-09\"\r\n },\r\n {\r\n \"SaleDate\":\u00a0\"2022-05-29\"\r\n },\r\n {\r\n \"SaleDate\":\u00a0\"2022-01-01\"\r\n }\r\n ]\r\n}\r\n<\/pre>\n$search with $filter<\/h2>\n
We can combine the $filter and $search together. The results include only those items that match both criteria. For example:<\/p>\n
GET http:\/\/localhost:5102\/odata\/sales?$search=”2022″ OR “2018”&$select=SaleDate,Id&$filter=Id gt 8<\/em><\/strong><\/p>\n
The response should look like:<\/p>\n
{\r\n \"@odata.context\":\u00a0\"http:\/\/localhost:5102\/odata\/$metadata#Sales(SaleDate,Id)\",\r\n \"value\":\u00a0[\r\n {\r\n \"Id\":\u00a09,\r\n \"SaleDate\":\u00a0\"2022-05-29\"\r\n },\r\n {\r\n \"Id\":\u00a011,\r\n \"SaleDate\":\u00a0\"2022-01-01\"\r\n }\r\n ]\r\n}\r\n<\/pre>\nThat\u2019s all.<\/p>\n
Summary<\/h2>\n
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<\/a>. Thanks.<\/p>\n
- QueryBinderContext <\/strong>holds the information used in binding.<\/li>\n<\/ul>\n
- as<\/em><\/strong> is the keyword and must have one in one compute expression.<\/li>\n
- Address<\/strong>: the complex type<\/li>\n
- Product<\/strong> & Sale<\/strong>: the entity types<\/li>\n
- $search<\/strong><\/a>: allows clients to request items within a collection matching a free-text\u00a0search expression<\/a>.<\/li>\n<\/ul>\n