OData v2 filter by property of $expanded entity

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)


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.