You are exposing an OData 4.01 entity set to many client teams; how do you keep arbitrary $filter, $expand and $orderby queries from becoming too expensive?

## Why an open query language is a cost problem OData gives the client a query language in the URL. That is its appeal — one endpoint serves many screens — and its risk: the client chooses the work. A few characters can ask for a lot: - `$filter=contains(Notes,'late')` on an unindexed text column scans the whole table. - `$expand=Orders($expand=Items($expand=Product))` turns a list into a tree three hops deep. - `$orderby` on an unindexed property forces a sort of the full filtered set before paging. - `$count=true` over a large set computes an exact total on every request. - `$filter=Orders/any(o:o/Items/any(i:i/Quantity gt 100))` is a nested semi-join per customer. ## What the specification lets a service refuse The OData 4.01 Protocol gives a service explicit room to support less: 1. An OData service **MAY support some or all** of the system query options. If it does not support one, it **MUST fail** any request containing it and **SHOULD return `501 Not Implemented`**. 2. At the Intermediate conformance level a service MUST support `$filter` with `eq` and `ne` on the entity set's properties, SHOULD support the other operators and canonical functions, and **MUST return 501** for any operator or function it does not support. 3. `$levels=max` is optional: services MAY support it. 4. Server-driven paging is always the service's decision; the `maxpagesize` preference is only a request. Failing is the point. A service that silently ignored an unsupported `$filter` would return unfiltered data that the client believes is filtered. ## Advertising the limits The Capabilities vocabulary lets a service tell clients in its metadata what it permits, so generated clients and UIs can avoid sending what will fail: | Term | What it can say | |---|---| | `FilterRestrictions` | `Filterable`; `RequiresFilter`; `RequiredProperties`; `NonFilterableProperties`; `FilterExpressionRestrictions` with `AllowedExpressions` such as `SingleValue`, `SingleRange` or `SearchExpression`; `MaxLevels` for traversal depth | | `SortRestrictions` | `Sortable`; `NonSortableProperties`; `AscendingOnlyProperties`; `DescendingOnlyProperties` | | `ExpandRestrictions` | `Expandable`; `MaxLevels`; `NonExpandableProperties` | | `SearchRestrictions` | `Searchable`; `UnsupportedExpressions` | | `CountRestrictions` | `Countable`; `NonCountableProperties` | | `TopSupported`, `SkipSupported`, `ComputeSupported` | tags saying whether those options work on a collection | | `FilterFunctions` | the functions and operators allowed in filter expressions | A `MaxLevels` of `-1`, the default, means no restriction — so leaving it unset advertises unlimited depth. `RequiresFilter` with `RequiredProperties` is a strong tool for very large sets: a client must always narrow by, say, `CustomerID` or a date range. `FilterExpressionRestrictions` names a property and the subset of expressions allowed on it, through `AllowedExpressions`: - `SingleValue` — the property can be used in a single `eq` clause. - `MultiValue` — several `eq` and `in` clauses combined by `or`. - `SingleRange` — one closed, half-open or open interval, such as `OrderDate ge 2026-01-01 and OrderDate lt 2026-02-01`. - `MultiRange` — a union of such intervals. - `SearchExpression` — a string property used with `startswith`, `endswith` or `contains`, combined by `or`. A date restricted to `SingleRange` maps naturally onto an index range scan, which is exactly the kind of query a service can promise to serve cheaply. ## Enforcing at run time The annotations are documentation that machines can read; they do not stop a request. The service must: - parse every query and check it against the same rules it advertises, failing violations rather than ignoring them; - page every collection, including each expanded collection, at a size it chooses, returning next links; - refuse `$expand` beyond its depth limit, and decline `$levels=max` if it does not support it; - apply its own guards the specification does not define — a statement timeout, a ceiling on `$top`, rate limits or per-client query budgets. Those are implementation choices, and the specification names no status code for a well-formed query that breaks such a self-imposed limit, so that choice is the service's too. ## The trade-off behind the limits Every restriction moves work back to the client: a client that cannot expand makes more round trips, and one that cannot filter on a property downloads more rows. The useful design move is to restrict along the lines of the storage — filterable and sortable where indexed, expandable where the join is cheap, required filters where the set is huge — and to publish those lines in metadata, so client teams learn the boundary before production rather than from a 501.