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)

Answer 2

It looks like the problem is the slash-escaped double quote and that you're using substring instead of substringof.

Does it work if you use single quotes instead? Or did you want to literally match the double-quote character? (If that's the case, I think you can just leave the double quote unescaped inside the single quotes)

_vti_bin/listdata.svc/Posts?$expand=Category&$filter=substringof('Featured Article',Category/Title) eq false

or

_vti_bin/listdata.svc/Posts?$expand=Category&$filter=substringof('"Featured Article"',Category/Title) eq false

As an example on a public service, take a look at this query:

http://services.odata.org/Northwind/Northwind.svc/Products?$filter=substringof('Bev', Category/CategoryName) eq true

As far as documentation for the OData query syntax, I recommend taking a look at this page: http://www.odata.org/documentation/odata-v3-documentation/url-conventions

In the filter section there are a fair number of examples you can use for guidance.