Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

OData $filter with items in a $expand

I have given some web services to access informations.

The first thing that i have tries to expand a node . And i have done that successfully with following code

http://www.domain.com/ODataService/WorkService.svc/CaseStudies?format=json&$expand=ServiceOfferings

Now i want to filter ServiceOfferingID that i will get when expanding ServiceOfferings . How can use filter option against a expanded collection

http://www.domain.com/ODataService/WorkService.svc/CaseStudies?format=json&$expand=ServiceOfferings&$filter=ServiceOfferings.ServiceOfferingID eq 127 

But its not working. What is right way to do the same

like image 287
Null Pointer Avatar asked Feb 07 '12 05:02

Null Pointer


People also ask

How do you use $expand in OData?

OData query option $expand is used to read multiple entities or entity sets in a single service call instead of two different calls. Prerequisite, entity sets which are used should be associated. To know about Association in OData service click here.

How does $filter work in OData?

The $filter system query option allows clients to filter the set of resources that are addressed by a request URL. $filter specifies conditions that MUST be met by a resource for it to be returned in the set of matching resources. The semantics of $filter are covered in the OData:Core document.

How do you handle special characters in OData query?

Do not use the “JavaScript String replace() Method”. It will replace the first occurrence of the special characters. If you have 2 occurance of the same special characters in the filtering parameter, it will fail. So use the regular expression to replace the characters.

What is $value in OData?

The $value option is used to get individual properties of an Entity. There are two ways to get individual properties from an entity. We can get the response in either OData format or get the raw value of the property. We need to add method to the controller named GetProperty here property is a name of the property.


3 Answers

The query you'll need to write depends on the cardinality of the expanded collection.

Here are some examples that use the public sample OData Northwind service, provided by odata.org.

An order is always done by exactly one customer.

Find the orders made by a customer with a specific name: http://services.odata.org/V3/Northwind/Northwind.svc/Orders?$expand=Customer&$filter=Customer/CompanyName eq 'Vins et alcools Chevalier'. This is equivalent to the answer of Dhawal.

A customer can issue many orders.

Use the quantifiers all or any to specify whether you want at least one, or all of the orders to obey your conditions.

  1. Find customers for which one or more orders have been processed by a specific employee: http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$expand=Orders&$filter=Orders/any(o: o/EmployeeID eq 9)
  2. Find customers that haven't ordered anything for a long time: http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$expand=Orders&$filter=Orders/all(o: o/OrderDate lt DateTime'1997-01-01')

You can call http://services.odata.org/V3/Northwind/Northwind.svc/$metadata and inspect the NavigationProperty elements, to see which relations exist.

<NavigationProperty Name="Orders"      Relationship="NorthwindModel.FK_Orders_Customers"      ToRole="Orders"      FromRole="Customers"/> 

Then, look for an association with that name and you'll find the cardinality:

<Association Name="FK_Orders_Customers">     <End           Type="NorthwindModel.Customer"           Role="Customers"           Multiplicity="0..1"/>     <End           Type="NorthwindModel.Order"           Role="Orders"           Multiplicity="*"/>     ... 

Navigating a one-to-many relationship like this: http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$expand=Orders&$filter=Orders/EmployeeID eq 9, will give you: "The parent value for a property access of a property 'EmployeeID' is not a single value. Property access can only be applied to a single value."

Navigating a many-to-one relationship with all or any, like http://services.odata.org/V3/Northwind/Northwind.svc/Orders?$expand=Customer&$filter=Customer/any(c: c/CompanyName eq 'Vins et alcools Chevalier'), will give you: "Any/All may only be used following a collection."

By the way, all() and any() are actually the Universal quantifier, ∀() and the existential quantifier, ∃(), respectively, which you may remember from math class.

like image 177
R. Schreurs Avatar answered Sep 23 '22 18:09

R. Schreurs


Filtering by child object's properties is supported in oData.

Here is an example: http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=Customer/Country eq 'Germany'

like image 20
Dhawal Avatar answered Sep 23 '22 18:09

Dhawal


In OData the Filter command only works on the top level element. For your filter to work you would need to have the following URL

http://www.example.com/ODataService/WorkService.svc/CaseStudies(x)/ServiceOfferings?format=json&$filter=ServiceOfferingID eq 127

Obviously this isn't the query you are trying to write, but behind the scenes your query is being converted to an expression tree which has a root expression based on the top level element.

If you really required to filter the data you could potentially intercept the query and write your own expression as below:

[QueryInterceptor("CaseStudies")]
public Expression<Func<CaseStudie, bool>> CaseStudieFilter()
{
    <Expression here>
}
like image 32
SCB Avatar answered Sep 23 '22 18:09

SCB