One thing that folks in this list and elsewhere have brought up is the need to express filters based on the contents of a collection, be that a set of related entities or a multi-valued property.
This is because currently there is no easy way to:
- find all movies starring a particular actor
- find all orders where at least one of the order-lines is for a particular product
- find all orders where every order-line has a value greater than $400
- find all movies tagged 'quirky' - where Tags is a multi-valued property.
- find all movies with a least one actor tagged as a favorite
This proposal addresses that by introducing two new operators, "any" and "all".
Relationships
For example it would be nice if this:
~/Movies/?$filter=any(Actors,Name eq 'John Belushi')
could be used to find any John Belushi movie. And this:
~/Orders/?$filter=any(OrderLines,Product/Name eq 'kinect')
would find orders that include one or more 'kinect' orderline. And this:
~/Orders/?$filter=all(OrderLines, Cost gt 400.0m)
would find orders where all orderlines costs more than $400.
Multi-Valued properties
All of the above query relationships, but this also needs to work for Multi-Value properties too:
The key difference here from a semantics perspective - is that a multi-valued property is often just a primitive, rather than an entity - so you often need to refer to 'it' directly in comparisons, perhaps something like this:
~/Movies/?$filter=any(Tags, it eq 'quirky')
In this example Tags is a multi-valued property containing strings. And 'it' or 'this' or something similar is required to refer to the current tag.
Given the need to be able refer to the 'current' item being tested in the predicate, perhaps we should force the use of 'it' even when the thing referred to is an entity?
Which would mean this:
~/Movies/?$filter=any(Actors, Name eq 'John Belushi')
Would need to become this:
~/Movies/?$filter=any(Actors, it/Name eq 'John Belushi')
Interestingly forcing this would conceptually allow for queries like this too:
~/Movies/?$filter=any(Actors, it eq Director)
Here we are looking for movies where an actor in the movie is also the movie's director.
Note: Today OData doesn't allow for entity to entity comparisons like above, so instead you'd have to compare keys like this:
~/Movies/?$filter=any(Actors, it/ID eq Director/ID)
Nested queries
Our design must also accommodate nesting too:
~/Movies/?$filter=any(Actors,any(it/Tags, it eq 'Favorite'))
Here we are asking for movies where any of actors are tagged as a favorite.
Notice though that 'it' is used twice and has a different meaning each time. First it refers to 'an actor' then it refers to 'a tag'.
Does this matter?
Absolutely if you want to be able to refer to the outer variable inside an inner predicate, which is vital if you need to ask questions like:
- Find any movies where any of the actors is tagged as a favorite
- Find any movies where an actor has also directed another movie
Clearly these are useful queries.
LINQ handles this by forcing you to explicitly name your variable whenever you call Any/All.
from m in ctx.Movies
where m.Actors.Any(a => a.Tags.Any(t => t == "Favorite"))
select m;
from m in ctx.Movies
where m.Actors.Any(a => a.Movies.Any(am => am.Director.ID == a.ID))
select m;
If we did something similar it would look like this:
~/Movies/?$filter=any(Actors a, a/Name eq 'John Belushi')
This is a little less concise, but on the plus side you get something that is much more unambiguously expressive:
~/Movies/?$filter=any(Actors a, any(a/Movies m, a/ID eq m/Director/ID))
Here we are looking for movies where any of the actors is also a director of at least one movie.
Final Proposal
Given that it is nice to support nested scenarios like this, it is probably better to require explicit names.
Trying to be flexible for the sake of a little conciseness often leads to pain and confusion down the road, so let's not do that J
That leaves us with a proposal where you have to provide an explicit alias any time you need Any or All.
Per the proposal here are some examples of valid queries:
~/Orders/?$filter=all(OrderLines line, line/Cost gt 400.0m)
~/Movies/?$filter=any(Actors a, a/Name eq 'John Belushi')
~/Movies/?$filter=any(Actors a, a/ID eq Director/ID)
~/Movies/?$filter=any(Actors a, any(a/Tags t, t eq 'Favorite'))
~/Movies/?$filter=any(Actors a, any(a/Movies m, m/Director/ID eq a/ID))
Summary
Adding Any / All support to the protocol will greatly improve the expressiveness of OData queries.
I think the above proposal is expressive, flexible and unambiguous, and importantly it also addresses the major limitations of MultiValue properties that a few of you were rightly concerned about.
As always I'm very keen to hear your thoughts…
Alex.