OData $filter with items in a $expand

Question

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

Answer 1

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'

Answer 2

You can also accomplish this through a webget on the service. I have had to do something similar to filter by properties of properties.

Answer 3

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.

Answer 4

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