OData v2 filter by property of $expanded entity

Question

I am using $expand to enhance an OData SharePoint REST query, and I would like to $filter on one of the properties of the expanded entity. However, I can't find any documentation on the correct syntax for this. I've found a few spots that might suggest using Entity/property, but after trying that, I fail with:

Query:

_vti_bin/listdata.svc/Posts?$expand=Category&$filter=substring(\"Featured Article\",Category/Title) eq false and year(Published) lt " +year+1+ " and month(Published) lt " +month+1+ " or day(Published) lt " +day+1+ " and ApprovalStatus eq '0'&$select=Title,Published,Category,ApprovalStatus&$orderby=Published desc"

Which returns:

syntax error '\"' at position 10.

How would I filter based on the Title of the Category entity, when Category is a high level, and it's Title property is a sub-level?

Answer 1

The filter on navigation entities is independent of the expand.

• If the navigation property is a not collection (1:1 relation)

  You can filter with type/property, here is an example to search for all orders that are made by customers with some id: http://services.odata.org/V2/Northwind/Northwind.svc/Orders?$filter=Customer/CustomerID%20eq%20%27ANATR%27

• If the navigation property is a collection (1:N relation):

  • OData Version 2: when the expanded entity is an entity collection, you cannot search on both the expanded and the target entity. For example you cannot do that query http://services.odata.org/V4/Northwind/Northwind.svc/Customers?$expand=Orders&$filter=Orders/ShipCity%20eq%20%27Berlin%27

    A work around might be to flip the query (to selecting the orders instead of customers - each order has 1 cutomer): http://services.odata.org/V2/Northwind/Northwind.svc/Orders?$filter=ShipCity%20eq%20%27Berlin%27&$expand=Customer but then you could get duplicate customers (your actual target entity).

  • OData Version 3 You might consider to use a custom query option (http://www.odata.org/documentation/odata-version-3-0/url-conventions/).

  • OData Version 4: you can do a filter with the any construct: http://services.odata.org/V4/Northwind/Northwind.svc/Customers?$filter=Country%20eq%20%27Germany%27%20and%20Orders/any(o:o/ShipCity%20eq%20%27Berlin%27)