In OData v4, how does `Customers?$filter=Orders/any(o:o/Amount gt 500)` differ from `Customers?$expand=Orders($filter=Amount gt 500)`?

## Two different questions The two URLs look alike but answer different questions, because a `$filter` always filters **the collection it is attached to**: | Request | Collection filtered | Customers returned | Orders in the response | |---|---|---|---| | `Customers?$filter=Orders/any(o:o/Amount gt 500)` | `Customers` | only those with at least one order over 500 | none — nothing is expanded | | `Customers?$expand=Orders($filter=Amount gt 500)` | each customer's `Orders` | all customers | per customer, only orders over 500 | The first is "which customers have a big order?"; the second is "show me every customer, with their big orders". A worked example makes the difference concrete. Suppose three customers: **Arnaud** has orders of 800 and 120, **Bertin** has one order of 90, and **Cordier** has none. - The `any` request returns only Arnaud, with no orders in the body. - The expand request returns Arnaud with the 800 order inline, Bertin with an empty `Orders` array, and Cordier with an empty `Orders` array. - An `all(o:o/Amount gt 500)` filter would return only Cordier — Arnaud fails on the 120 order, Bertin on the 90 one, and Cordier passes because he has no orders at all. ## How lambda operators work OData v4 (URL Conventions, section 5.1.1.13) defines two **lambda operators** that evaluate a Boolean expression on a collection. Both must be prepended with a navigation path that identifies a collection. - The argument is a **lambda variable** name, a colon and a Boolean expression: `o:o/Amount gt 500`. The variable stands for each member of the collection in turn, so `o/Amount` is an order's amount. - **`any`** returns true if and only if the expression is true for at least one member. That implies it is **false for an empty collection**. - `any` also has a **short form with no argument**: `Orders/any()` is false only when the collection is empty, so it means "has at least one order". - **`all`** returns true if the expression is true for every member. That implies it is **true for an empty collection**, and it cannot be used without an argument. Scope inside the expression follows explicit rules. A path prefixed with the lambda variable refers to the member. A path with neither the variable nor `$it` is evaluated in the scope of the instances **at the origin of the navigation path** — the customer: ```http GET /odata/Customers?$filter=Orders/any(o:o/ShippingAddress ne Address) ``` Here `Address` is the customer's address, so this finds customers with an order shipped somewhere else. If the variable's name collides with a property of the current resource, the variable wins; `$it` is the way to reach the current resource's property explicitly. ## Filtering the children instead A `$filter` inside the parentheses of an expand item is an **expand option**. It is evaluated for each customer's related orders and keeps only matching ones in the inline `Orders` array. It has no effect on which customers are selected: the outer collection is filtered only by an outer `$filter`, so customers with no qualifying orders still appear, with an empty array. ## Combining the two Most reporting screens want the intersection — customers with a large order, showing those orders: ```http GET /odata/Customers?$filter=Orders/any(o:o/Amount gt 500) &$expand=Orders($filter=Amount gt 500;$orderby=Amount desc) ``` The condition is deliberately repeated: the outer lambda selects parents, the inner filter trims children. Writing only one of them is the classic source of either too many customers or too many orders. ## Edge cases that are asked about 1. **`all` on an empty collection.** `Customers?$filter=Orders/all(o:o/Amount gt 500)` includes customers who have **no orders**, because `all` is vacuously true. Add `Orders/any()` with `and` to exclude them. 2. **Nesting.** Lambdas nest: `Orders/any(o:o/Items/any(i:i/Quantity gt 100))` finds customers with an order holding a large line. 3. **Counting instead.** `$filter=Orders/$count gt 5` uses the exact count of related entities, which is a different test from any lambda. 4. **Nulls.** Items for which the `$filter` expression evaluates to null are omitted, just like false. ## Why interviewers like this pair It checks whether a candidate reasons about **which set** an expression ranges over, and it has a cost angle: a lambda over a large related collection is a semi-join the service must evaluate for every parent, which is one reason a service may restrict how deep filter expressions may traverse.