Search code examples
filterodata

Odata filtering - find empty relationship


I have a database many-to-many relationship. I'd like to use Odata to filter on which parents have no children.

Example. Student and Classes. Students may have zero, one, or many classes. Looking for students in a specific class is easy.

?$filter=Classes/any(x: x/Name eq 'Math 101')

Multiple is also easy

?$filter=Classes/any(x: x/Name eq 'Math 101' or x/Name eq 'English 101')

But my question is, how do I find Students with no classes. None of the following work.

?$filter=Classes/any(x: x/Name eq '')
?$filter=Classes/any(x: x/Name eq null)
?$filter=Classes eq null

I feel like this should be easy, however my google-fu has failed me. Perhaps there is some kind of Classes.count() that I can compare to zero?

Thanks in advance.


Solution

  • You can use a not in front of an empty any like this:

    ?$filter=not Classes/any(x: true)
    

    Here is a working example on the TripPin OData example service:

    http://services.odata.org/V4/(S(azcw4gs1z4x1ir2e5hrfkukj))/TripPinServiceRW/People?$filter=not%20Trips/any(x: true)