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

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.