Share via

$filter Query Option in ADO.NET Data Services

  • Article

Let's say that we have a data service exposing all of Northwind. We can get all customers in the database by accessing this URL.

https://localhost/WebDataService1.svc/Customers

That could be more data than we wanted. Let's say we only care about customers in London. We can use the $filter query option to get all customers that match this condition by accessing this URL.

/Customers?$filter=City eq 'London'

(I’m shortening and unescaping such URLs for readability; we actually follow standard URI conventions and the URI you’ll see will be something like https://localhost/WebDataService1.svc/Customers?$filter=City%20eq%20'London')

What can go in a $filter? The most basic thing to do is to test properties of the resources we're returning, which you can access simply by name, like we have done above. Literals for things like strings and numbers use the same syntax as in the key portion (see my last post and the original URL post for more details).

To actually test for a condition, you can use comparison operators, which look a lot like the symbolic ones you'd expect in C# but use short mnemonics to make them easier to write and not have to deal with escaping/unescaping in the URL.

eq equals
ne not equals
lt less than
le less than or equal
gt greater than
ge greater than or equal

So all of these are valid URLs:

  • /Orders?$filter=OrderDate lt '1996-08-01'
  • /Orders?$filter=Customers/CustomerID eq 'ALFKI' -> Note that you can traverse navigation properties that reference a single resource. The "Customers" plural is an artifact of the default mapping generated, the property is really a to-one navigation property.

To include more than one condition, you can use 'and', 'or' and of course 'not'.

  • /Orders?$filter=Customers/CustomerID eq 'ALFKI' or Customers/CustomerID eq 'WOLZA'
  • /Orders?$filter=ShipCountry eq 'France' and ShipCity eq 'Toulouse'

To group conditions logically, you can use parenthesis.

  • /Orders?$filter=ShipCountry eq 'France' and (ShipCity eq 'Toulouse' or ShipCity eq 'Marseille')

Today we don’t support operations on collections, which has two practical points.

  • First, we don’t lift values from collections, so you can’t have a filter such as /Customers?$filter=Orders/Name eq 'Foo' (this would imply a quantifier such as ‘any’ or ‘all’).
  • Second, we don’t have aggregates, so you can’t write a query such as /Customers?$filter=count(Orders) gt 5. This restriction helps to set bounds to any single operation and simplifies the queries that the server and clients need to deal with.

Next time, more on what you can put in a $filter. 

This post is part of the transparent design exercise in the Astoria Team. To understand how it works and how your feedback will be used please look at this post.

Comments