Which range or set queries still need raw SQL or client evaluation?

[CaffeinatedCoder]

Most of the range and set API already runs in PostgreSQL when you use it in a LINQ query. Some members still run in memory after the rows are loaded, and a few aren't available in a query at all. What runs where lists every public query member and where it runs today.

The next translations are planned in the 10.3.0 epic:

• Length on Int32Range and Int64Range (Translate Length on Int32Range and Int64Range #405)
• Clamp on Int32Range, Int64Range and DecimalRange (Translate Clamp on Int32Range, Int64Range and DecimalRange #406)
• the calendar factories over a column: Month, Quarter, Year, IsoWeek (Translate the calendar factories when they read a column #191)

Enumeration, the bridge conversions, and value set Intersect/Except are a separate batch (#407).

This thread is for the other side of that list: which query do you need to run in the database, and can't today? A concrete example is worth more than a vote. If you have one, please share:

1. A small LINQ query, with the entity it runs over.
2. The result you expected, and what you get now (an exception, a query that loads more rows than it should, or raw SQL you wrote instead).
3. Why it matters that it runs in PostgreSQL: table size, an index you want it to use, or something that has to happen inside ExecuteUpdate or ExecuteDelete.

It also helps to hear what you're comparing the library with, and anything else that keeps you from adopting it: documentation, compatibility, behaviour, or support.

Good to know:

• Status lives in the issues. When a request here is accepted, it becomes an issue under its release epic, and this thread links it. The epic says what ships when.
• A translation has to give the same answer as memory. Every new translation is checked against the in-memory result on a live server, across the five kinds of range, the type's extremes and nullable columns. A member whose SQL can't match memory exactly stays in memory for that type. The table in What runs where gives the reason for each member that stays in memory, such as Shift and Expand.
• A bug can go straight to an issue. If a query returns a wrong answer, please use the Bug form.