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

Short answer: change the dot to a slash, i.e. `&$filter=ServiceOfferings/ServiceOfferingID eq 127` should work. — BrainSlugs83, Sep 20 '16 at 21:29

R. Schreurs · Answer 1 · 2017-11-17T11:02:47.003

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.

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)
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.

Are you familiar with `Simple.OData.Client`? I'm curious if a filtered expansion is supported by it. — Shimmy Weitzhandler, Jul 29 '19 at 20:52

score 14 · Answer 2 · edited May 20 '15 at 13:10

14

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'

edited May 20 '15 at 13:10

VladL

12,769
10
63
83

answered Sep 28 '12 at 21:51

Dhawal

1,240
2
12
20

Do you have any idea if this is achievable with `Simple.OData.Client`? – Shimmy Weitzhandler Jul 29 '19 at 20:46

score 7 · Answer 3 · answered May 28 '21 at 09:54

7

Might be helpful for someone

GET serviceRoot/People?$expand=Trips($filter=Name eq 'Trip in US')

answered May 28 '21 at 09:54

Muhammad Tayyab

71
1
3

1

@Muhammad_Tayyab Do you know how you would go about formatting the url to only return results where the expand dataset is not null? For example Trips might equal "Trips": [] – Joey Lawrence Sep 24 '21 at 03:08
This indeed the format I expect, but the service I talk to (Microsoft Dataverse) ignores the filter and returns all items. – Creative Jul 26 '22 at 21:59
@Creative Did you figure this out? Im having the same issue where I use the filter but its ignored and returns everything. – sbonkosky Feb 16 '23 at 15:43

score 6 · Answer 4 · edited Jul 29 '19 at 20:47

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

@nil if you read the question they are actually talking about filtering on an expand which is what my answeer addresses, this is different to filtering on a complex type, so although you may say this is not true it is for expands — SCB, Jun 18 '13 at 22:40

score 0 · Answer 5 · answered Feb 07 '12 at 18:41

0

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

answered Feb 07 '12 at 18:41

George

430
1
4
16

OData $filter with items in a $expand

5 Answers5

Linked