Repository navigation
Translate enumeration of a multirange column: Count, Any, All, SelectMany over a RangeSet (unnest) #94
Description
unnest(anymultirange) → SETOF anyrange is the one multirange function with no translation. RangeSet<TRange, T> is an IReadOnlyList<TRange>, so a consumer naturally writes:
bookings.Where(b => b.Blocked.Count > 2) // how many separate blocks
bookings.Where(b => b.Blocked.Any(r => r.Length > 7)) // a block longer than a week
bookings.SelectMany(b => b.Blocked) // one row per blockToday none of these translate: RangeSet is mapped as a scalar, so EF has no table-valued source for it. In a predicate that throws (correct, fails loudly); in a projection EF loads the row and runs it in memory.
Verified live: select * from unnest(int4multirange(int4range(1,3), int4range(5,8))) → [1,3), [5,8); (select count(*) from unnest(m)) is the element count.
Proposed translation: Count → (SELECT count(*) FROM unnest(m)); Any/All/Count(predicate) → EXISTS (SELECT 1 FROM unnest(m) AS r(value) WHERE …) with the range mapping on r.value so the range operators apply inside; SelectMany → a CROSS JOIN LATERAL unnest(m). The same shape Npgsql uses for its own array Any translation and that ValueRangesQueryExpressionInterceptor already produces for a captured list of ranges (FROM unnest(@wanted) AS w(value)).
Scope note: Count is the element count of the canonical multirange, which is what the in-memory Count is too, so there is no canonical-form caveat like the one array_cat has.
Done when: Count, Any(predicate), All(predicate) and SelectMany over a multirange column translate, the translated SQL is asserted in the no-database tests, and a live test agrees with memory for a set with 0, 1 and 3 elements.
Found by the PostgreSQL catalogue sweep (pg_proc over anymultirange), 2026-10-17.
Activity
CaffeinatedCoder commentedon Oct 18, 2026
Spike, 2026-10-18 (EF Core 10, Npgsql EF 10.0.0). Not done for 10.0.0. Turning the scalar-mapped multirange column into a table source has no clean seam. Findings:
Today.
b.SeatBlocks.Count > 2,Any(r => …),All(r => …),Count(r => …)in a predicate, andSelectMany(b => b.SeatBlocks), all fail translation withInvalidOperationException.Countin a projection loads the column and counts in memory. So the failure is loud, as the issue says.Route 1: give the multirange mapping an element mapping (the range mapping), as Npgsql's array and multirange mappings have. EF then classifies every
RangeSetproperty as a primitive collection, and model building fails for every query, not only these:The type 'RangeSet<DateRange, DateOnly>' cannot be used as a primitive collection because it is not an array and does not implement 'IList<DateRange>'. That is the EF primitive-collection takeover the scalar-mapping design avoids (the same reason value sets must not implementIEnumerable<T>). Rejected.Route 2: route 1 plus
IList<TRange>onRangeSet, read-only with the mutators throwing. EF now gets as far asRelationalQueryableMethodTranslatingExpressionVisitor.TranslateMemberAccess→NpgsqlQueryableMethodTranslatingExpressionVisitor.TranslatePrimitiveCollection. That throwsUnreachableExceptionfor all six shapes, because Npgsql builds theunnestsource only for its ownNpgsqlArrayTypeMapping/ multirange / JSON mapping classes. Making it work would mean deriving the plugin's mapping from Npgsql's pubternal multirange mapping. It would also add a mutable-collection interface to a public immutable type on the LTS line, and flip every EF convention that keys on primitive collections (JSON columns, change tracking) for everyRangeSetproperty. Rejected for 10.0.0.Route 3: own the translation. EF calls
TranslatePrimitiveCollectiononly for a property flaggedIsPrimitiveCollectionor for a parameter, so a scalar column never reaches it. The plugin would have to subclass Npgsql's (pubternal)NpgsqlQueryableMethodTranslatingExpressionVisitorand replaceIQueryableMethodTranslatingExpressionVisitorFactory. Unlike the SQL processor factoryValueSetSqlGuarddecorates, this one cannot be decorated: the visitor's recursion is internal, so a wrapper never sees the nested member access. It would also buildSelectExpression/PgUnnestExpressionunder EF1001, on both the Npgsql 10 and 11 lines, and collide with any other extension that replaces the same factory.Countalone has the same problem one level down. It needs a scalar subquery(SELECT count(*) FROM unnest(m)), and a member translator has no alias manager to build one. PostgreSQL has no multirange cardinality function; the only non-subquery spellings parse the text form. Not clean.What already covers most of it. The predicates people write over the elements mostly have a set-level operator that runs as SQL and is index-served:
b.Blocked.Any(r => r.Overlaps(p))b.Blocked.Overlaps(p)(&&)b.Blocked.Any(r => r.Contains(v))b.Blocked.Contains(v)(@>)b.Blocked.Any(r => r.Contains(q))b.Blocked.Contains(q)(@>, a range lies inside one element)b.Blocked.All(r => r.IsContainedBy(p))b.Blocked.IsContainedBy(p)(<@, added by #92)b.Blocked.Any()!b.Blocked.IsEmpty()(isempty)Element-level shapes with no set operator (
Count,Any(r => r.Length > 7),SelectMany) stay in memory or need raw SQL.Where it could go. The 11.x line (#147) is the natural place to revisit route 2. That would be with a decision to make
RangeSetan EF primitive collection, and a measured list of what that changes for JSON columns and change tracking, ideally with a public seam from Npgsql for a custom multirange mapping'sunnest. It still carries the 10.0.0 milestone. Whether to move it to the 11.0.0 epic or the backlog is the maintainer's call. Nothing was committed from the spike.CaffeinatedCoder commentedon Oct 18, 2026
Moved back to the backlog (#83), as epic #58 said a stretch item would be if it wasn't ready. The spike above found no clean seam on EF Core 10 / Npgsql 10. The 11.x line is the candidate for route 2, as part of a decision on whether
RangeSetshould become an EF primitive collection. That decision gets its own issue when someone takes it up.