oData WCF Data Services Filter Child Collection

Question

I have an array of customers, each customer has a collection of orders.

the query below returns customers and their orders and works as expected:

~Customers?$expand=Orders

Now I want to get the existing customers but filter their orders by Order.Amount > 100 (I'm happy for the customers having no such orders to remain in the list),

When I'm trying the following:

~Customers?$expand=Orders&$filter=Orders/Amount gt 100

I'm getting the following error:

The 'Amount' is not allowed at position ***. Member access or specifying a type identifier on a resource set reference is not allowed.

I can loop through the customers and call

~Customers('Blah')/Orders?$filter=Amount gt 100 

which works, but I'd really like to get it done in one go.

Could you advice on how do I accomplish this?

Answer 1

There are two things causing problems here. First, it sounds like you want to filter the Orders collection, not the Customers collection (i.e., you always want all Customers to be returned, but you only want certain Orders to be expanded). When you have a URI that looks like EntitySet?$filter=<some predicate>, the filter always applies to the EntitySet, which in your case is Customers (which is not what you want).

Secondly, the reason ~Customers?$expand=Orders&$filter=Orders/Amount gt 100 doesn't even filter the Customers correctly is because Orders is a collection of entities, not a single entity. Orders/Amount doesn't make sense if you think of Orders as a list, only Order/Amount does. If you wanted to have a filter that only returns Customers who have at least one Order where Order/Amount is greater than 100, you could use the any keyword:

~Customers?$expand=Orders&$filter=Orders/any(o: o/Amount ge 100)

But again, that filters the Customers, not the Orders.

Given that you want to actually be filtering the Orders, would the following work for you?

~Orders?$expand=Customer&$filter=Amount gt 100