OData Filter by an M-M relationship

Is it possible to filter in odata by the linked entities in an M-M relationship where it must contain all of the second, but allow extras?

Suppose I've got: Student StudentClass Class and I want to find all students where their enrolment includes the classes (101, 102, 103)

Solution

Yes, OData v4 provides 2 Lambda Operators that evaluate a Boolean expression on a collection. These are Any and All, where Any returns the record if atleast one of the child entries matches the criteria, and All requires all of the child records to match.

In your case we can use Any:

5.1.1.10.1 any
The any operator applies a Boolean expression to each member of a collection and returns true if the expression is true for any member of the collection, otherwise it returns false. The any operator without an argument returns true if the collection is not empty.

Example 79: all Orders that have any Items with a Quantity greater than 100

http://host/service/Orders?$filter=Items/any(d:d/Quantity gt 100)

however we can't pass through an array or list of values to compare against so we have to break this query down into an Any expression for each value and AND them together.
This sounds counter intuitive but the following simple query where we use OR inside a single Any results in matches where the Student has one or more of the classes (101,102,103) but not neccessarily all of them:

http://host/service/Students?$filter=Enrollments/Any(e:e/Class/Code eq '101' or e/Class/Code eq '102' or e/Class/Code eq '103')

if they must be enrolled in atleast all of 101,102,103 then we must AND the results of a separate Any operator for each of the classes, below is the 3 separate queries for each class and a final query that ANDs them together:

http://host/service/Students?$filter=Enrollments/Any(e:e/Class/Code eq '101')
http://host/service/Students?$filter=Enrollments/Any(e:e/Class/Code eq '102')
http://host/service/Students?$filter=Enrollments/Any(e:e/Class/Code eq '103')

http://host/service/Students?$filter=Enrollments/Any(e:e/Class/Code eq '101') AND Enrollments/Any(e:e/Class/Code eq '102') AND Enrollments/Any(e:e/Class/Code eq '103')

These queries assume:
there is a navigation property on the Student record called Enrollments that links the Student to a collection of StudentClass records,
StudentClass has a navigation property called 'Class' that links it to a single Class record,
the class codes (101,102,103) are stored in a string property on the Class record called Code

NOTE: Different providers support these Lambda operators to different extents, you should check with your vendor/developer if you have have complex needs like support for nesting expressions with Any or All or if the simplest queries are not resolving as you expect.

This query does not specifically request that the Enrollments or the Class records be included in the results, it should only return a list of Students unless there is server-side configuration or logic to $expand these navigation properties by default.

While not needed by OP, if you are expanding these same object graphs (that you are filtering by), it is important to point out that using the Any operator in the root $filter query option will not apply filtering to the expanded child collection. If you wanted to return ONLY the child records that matched the expression you would need to implement a $filter expression within the $expand query option, in addition to the Any operator in the root $filter query option.

The reverse is also true, simply using the $filter within the $expand query option will not prevent the top level Student from being returned if they were only enrolled in other classes, it wouldn't return any classes because they didn't match the criteria, but it would still return the Student record.

For this reason, when using $filter nested inside $expand it is very common to include an Any or All operator in the $filter query option.