CodoMetis.ValueRanges.EFCore.PostgreSQL
10.2.0
Prefix Reserved
See the version list below for details.
dotnet add package CodoMetis.ValueRanges.EFCore.PostgreSQL --version 10.2.0
NuGet\Install-Package CodoMetis.ValueRanges.EFCore.PostgreSQL -Version 10.2.0
<PackageReference Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" Version="10.2.0" />
<PackageVersion Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" Version="10.2.0" />
<PackageReference Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" />
paket add CodoMetis.ValueRanges.EFCore.PostgreSQL --version 10.2.0
#r "nuget: CodoMetis.ValueRanges.EFCore.PostgreSQL, 10.2.0"
#:package CodoMetis.ValueRanges.EFCore.PostgreSQL@10.2.0
#addin nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.2.0
#tool nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.2.0
CodoMetis.ValueRanges.EFCore.PostgreSQL
This package lets you store the types of
CodoMetis.ValueRanges in PostgreSQL with
Entity Framework Core and Npgsql. A range goes into a range column, a RangeSet<TRange, T> into a
multirange column, and a value set into a native array column. Your LINQ queries over them run in
the database as SQL, and they return the same results as the same code in memory.
dotnet add package CodoMetis.ValueRanges.EFCore.PostgreSQL
It needs PostgreSQL 16 or later. You turn it on with one line. You don't configure value converters, comparers or column types:
options.UseNpgsql(connectionString, npgsql => npgsql.UseValueRanges());
Which column each type gets
EF Core picks the column from the property type:
| Property type | Column type |
|---|---|
Int32Range |
int4range |
RangeSet<Int32Range, int> |
int4multirange |
DateRange |
daterange |
RangeSet<DateRange, DateOnly> |
datemultirange |
TimeRange |
timerange (custom type — see below) |
Float64Range |
float8range (custom type — see below) |
Float32Range |
float4range (custom type — see below) |
Float64Set |
double precision[] |
Float32Set |
real[] |
StringSet |
text[] |
GuidSet |
uuid[] |
| … and so on for every type |
Sets over your own element types work too. A property such as StringSet<AccessRight> is
recognized from the open generic, so there is no registration per element type to forget. Plain
string[] and List<Guid> properties keep Npgsql's own array mapping, and they can sit in the same
model.
Good to know:
TimeRangeneeds two opt-ins. PostgreSQL has no built-intimerange. You callHasPostgresRangeto create the type, andEnableUnmappedTypeson the data source. The EF Core guide shows both in TimeRange and the custom timerange type.Float64RangeandFloat32Rangeneed the same two opt-ins. Create the types withHasPostgresRange("float8range", "double precision"), without asubtype_diff, which overflows a GiST index over very large values, andHasPostgresRange("float4range", "real"). Rows holdingNaNor an infinity throw when loaded. See Custom range types.- Sets are mapped as single values (scalars). The package brings its own mappings and translators for them. EF Core's primitive-collection support is never involved.
- Inside a
ToJson()column, a value is stored as its PostgreSQL text. This applies to a range, a multirange and a value set. A query into the document runs through a cast:CAST(doc ->> 'Period' AS daterange) @> @day. - That text is the database's form, not the JSON your API sends. A value set is stored as
"{a,b}", and theYearMonthtypes as month-aligned dates. A temporal value is stored at the server's microsecond, truncated the way Npgsql truncates a parameter, with the extremes as-infinityandinfinity. So the document, a column and a parameter all hold the same value. ExecuteUpdateof a value set inside a JSON document throws. You can set a range inside the document, and you can set the whole JSON property.- Scaffolding doesn't produce these types.
dotnet ef dbcontext scaffoldmaps range columns toNpgsqlRange<T>and array columns to plain arrays, because the package provides no design-time services. Change the property types yourself after scaffolding. Using these types is a decision about your model.
Query ranges
var day = new DateOnly(2024, 6, 15);
bookings.Where(b => b.Period.Contains(day)); // b."Period" @> @day
bookings.Where(b => b.Period.Overlaps(other)); // b."Period" && @other
bookings.Select(b => b.Period.Intersect(other)); // b."Period" * @other
bookings.OrderBy(b => b.Period.LowerBound()); // ORDER BY lower(b."Period"); no start sorts last
bookings.Select(b => b.Period.Merge(other)); // range_merge(b."Period", @other)
bookings.Select(b => b.BlockedDays.Gaps(month)); // datemultirange(@month) - b."BlockedDays"
bookings.Where(b => b.BlockedDays == someSet); // b."BlockedDays" = @set
bookings.GroupBy(b => b.CustomerId) // range_agg(b."Period")
.Select(g => g.Select(b => b.Period).RangeAgg());
Each comparison runs as one PostgreSQL operator:
| Method | Operator |
|---|---|
Contains |
@> |
Overlaps |
&& |
IsContainedBy |
<@ |
IsStrictlyLeftOf / IsStrictlyRightOf |
<< / >> |
DoesNotExtendLeftOf / DoesNotExtendRightOf |
&< / &> |
CompareTo against 0 |
<, <=, >, >=, = |
IsAdjacentTo runs as -|-. All of these work on ranges and on a RangeSet, with either a range
or a multirange as the argument.
You can combine ranges in the database too:
Intersectruns as*.UnionandExceptturn both sides into multiranges and run as+and-. That matches theirRangeSetreturn type. So the union of two disjoint ranges comes back as a real two-element multirange, not an error.- The factories
CreateFinite,CreateUnboundedStartandCreateUnboundedEndrun as range constructor calls with a guard. The guard keeps the model's rule: bounds in the wrong order give an empty range.
The state checks map to PostgreSQL's range functions:
| Method | SQL |
|---|---|
IsEmpty() |
isempty |
IsUnboundedStart() |
lower_inf |
IsUnboundedEnd() |
upper_inf |
IsInfinity() |
lower_inf AND upper_inf |
IsFinite() |
NOT lower_inf AND NOT upper_inf AND NOT isempty |
Good to know:
- Discrete ranges come back exactly as in memory. PostgreSQL stores
int4range,int8rangeanddaterangehalf-open, as[lower, upper). The model keeps them closed, as[lower, upper]. The package makes up for the difference.UpperBound()runs asupper(x) - 1, andUpperBoundInclusive()asNOT upper_inf(x) AND NOT isempty(x). Results from the database always equal the results in memory, checked against a live PostgreSQL. LowerBound()andUpperBound()returnT?. PostgreSQL'slowerandupperreturnNULLwhen that side is unbounded or the range is empty. The in-memory methods returnnullin the same cases.- Aggregates over zero rows return
NULLin SQL. That is standard PostgreSQL behaviour. In memory,RangeAgg()over nothing returns the empty set.RangeIntersectAgg()returnsnullin both places. DateTimeRangebounds are stored as wall-clock time. They are written astimestampwithDateTimeKind.Unspecified. A UTC-kindedDateTimeis reinterpreted as wall-clock time, not converted.DateTimeOffsetRangebounds are stored in UTC. They are normalized to UTC fortimestamptz. You keep the instant, but the original offset doesn't come back.DateTime.MinValueandMaxValueare stored as-infinityandinfinity. That is Npgsql's default rule. Such a bound is still a finite bound that happens to be infinite. It stays different from an unbounded side, soupper_infstaysfalse.
Query value sets
users.Where(u => u.Roles.Contains("admin")); // u."Roles" @> ARRAY['admin']::text[]
users.Where(u => u.Roles.Overlaps(required)); // u."Roles" && @required
users.Where(u => u.Roles.IsSubsetOf(granted)); // u."Roles" <@ @granted
users.Where(u => u.Roles.Count > 2); // cardinality(u."Roles") > 2
users.Where(u => u.Roles.Remove("admin").IsEmpty); // cardinality(array_remove(…)) = 0
Contains always runs as @>, never as = ANY, so a plain GIN index on the column serves it.
Good to know:
Unionis the one set operation whose SQL result isn't deduplicated and sorted. It runs asarray_cat, which joins two arrays without removing duplicates.- That is harmless in exactly two places. The operators
@>,<@and&&ignore duplicates. When you load the result, reading the set sorts and deduplicates it again. - Anything that depends on order or duplicates throws instead. That covers equality,
Count,GroupBy,Distinct,OrderByand a value inExecuteUpdate. The query throws rather than return a different answer from memory. The package checks this on the translated SQL.
What runs in memory only
These operations give the right answer in memory, and an EF Core query doesn't translate them.
Some would need composed SQL that isn't written; for others, SQL would answer differently. The
table says which.
In a Where, the query fails to translate. It fails loudly, instead of quietly fetching the whole
table. In the final Select, EF Core computes them in memory after it loads the rows.
| Operation | Why |
|---|---|
Intersect, Except on value sets |
not translated: filtering the left array keeps its order, but is canonical only over canonical rows, as Union is |
Add on value sets |
inserting in order means sorting in SQL, by the database collation rather than ordinal |
Length on any range |
not translated: the integer types need a widening cast and a branch for the empty range, and a temporal range open to infinity fails in PostgreSQL |
Values() and Values(step) on a discrete range, Split on any range |
not translated: each would be generate_series collected into an array |
Shift, Expand on any range |
not translated: rebuilding the range from lower(x) + d answers differently for the empty range, time past midnight and a bound moved past the last date .NET holds |
DateRange.Month, Quarter, Year and IsoWeek with an argument read from a column |
not translated yet (#191): PostgreSQL has no calendar-range function. Built from values your code passes in, the range is sent as one constant, so comparing a column against a month runs as SQL |
ToRangeSet() / ToInt32Set() and the other bridge conversions |
not translated: an array becomes a multirange by aggregating one-element ranges, and the reverse is enumeration |
Clamp(value) on any range |
not translated: it would be LEAST/GREATEST over the bounds, with branches for the empty range and a discrete upper bound |
the value set indexer, set[0] |
a row another tool wrote may not be canonical, arrays start at 1, and an index past the end is NULL where memory throws |
Everything else on the range, multirange and array surface runs in the database. The EF Core guide puts both halves side by side in What runs where.
Use NodaTime types
For NodaTime types, add
CodoMetis.ValueRanges.EFCore.PostgreSQL.NodaTime
and call npgsql.UseValueRangesNodaTime() instead.
Documentation
- The EF Core guide shows every translation with the SQL it generates.
- Getting started walks you through a first entity, migration and query.
- The changelog lists what changed in each version.
License
MIT — see LICENSE.
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
-
net10.0
- CodoMetis.ValueRanges (>= 10.2.0)
- Npgsql.EntityFrameworkCore.PostgreSQL (>= 10.0.0)
NuGet packages (1)
Showing the top 1 NuGet packages that depend on CodoMetis.ValueRanges.EFCore.PostgreSQL:
| Package | Downloads |
|---|---|
|
CodoMetis.ValueRanges.EFCore.PostgreSQL.NodaTime
NodaTime support for the CodoMetis.ValueRanges EF Core (Npgsql) plugin: maps LocalDateRange to daterange, LocalDateTimeRange to tsrange, InstantRange to tstzrange and YearMonthRange to a month-aligned daterange (plus their RangeSet<TRange, T> multirange counterparts), bridging through NpgsqlRange<T> via Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime. The full range algebra translates from LINQ to SQL exactly as for the BCL-based types — operators, bound accessors, range_merge, range_agg/range_intersect_agg, factories and multirange operations. v6 also maps the NodaTime value sets to native arrays — LocalDateSet to date[], LocalDateTimeSet to timestamp[], InstantSet to timestamptz[], LocalTimeSet to time[] (no CREATE TYPE needed, unlike timerange) and YearMonthSet to a month-aligned date[] — with the set algebra translating to the array operators (@>, &&, <@, cardinality). Enable with one line: options.UseNpgsql(..., npgsql => npgsql.UseValueRangesNodaTime()) — this implies both UseNodaTime() and UseValueRanges(). |
GitHub repositories
This package is not used by any popular GitHub repositories.
| Version | Downloads | Last Updated |
|---|---|---|
| 10.4.0 | 356 | 10/26/2026 |
| 10.3.0 | 598 | 10/25/2026 |
| 10.2.0 | 884 | 10/25/2026 |
| 10.1.0 | 769 | 10/19/2026 |
| 10.0.0 | 335 | 10/19/2026 |
| 9.1.0 | 1,602 | 10/17/2026 |
| 9.0.0 | 391 | 10/11/2026 |
| 8.0.0 | 441 | 8/17/2026 |
| 7.0.0 | 227 | 8/17/2026 |
| 6.3.0 | 177 | 8/16/2026 |
| 6.2.1 | 185 | 8/16/2026 |
| 6.2.0 | 254 | 8/15/2026 |
| 6.1.0 | 162 | 8/14/2026 |
| 6.0.0 | 159 | 8/14/2026 |
| 5.0.0 | 154 | 8/13/2026 |
| 4.1.0 | 142 | 8/12/2026 |
| 4.0.0 | 134 | 8/10/2026 |
| 3.1.0 | 170 | 6/17/2026 |
| 3.0.0 | 154 | 6/11/2026 |
Changed answers
- EF Core: LowerBound() and UpperBound() in a query are NULL for an empty range or set and on an unbounded side, as in memory. So == null selects those rows, where it selected none on a required column, and ?? fallback returns the fallback, where the row was missing (#354 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/354), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1020)).
- EF Core: a range, multirange or value set predicate over a NULL column is false, so its negation keeps the row, as EF Core does for string and string[]. !b.Period.Overlaps(p) dropped the NULL rows, and a projected predicate threw (#357 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/357), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1020)).
- EF Core: Contains(T) on a range or multirange is false in a query when the element is NULL: a column of a missing child in an outer join, or a nullable column read with .Value. Its negation dropped the row, and a projection threw (#387 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/387), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1020)).
- EF Core: CreateFinite, CreateUnboundedStart and CreateUnboundedEnd over a NULL element are NULL in a query, as EF Core's own AddDays is. They built empty or (,), so Contains(v) selected a row without the element (#388 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/388), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1020)).
Fixed
- Docs: What runs where (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#what-runs-where), the READMEs and the XML docs give each in-memory operation's real reason. Shift and Expand keep a bound at infinity, so that reason was stale. Length, Values, Split, the bridge conversions, Clamp, Intersect and Except would take composed SQL, not a missing operator. In-memory operations run in the final Select only.
- Docs: the What runs where (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#what-runs-where) tables list every public member a query can call. Equals runs as =. Values on a value set, Count and the indexer on a RangeSet, From, Parse, the NodaTime conversions, ToString, GetHashCode and GetEnumerator run in memory.
- Docs: two queries in 10.2.0 still return something other than the same LINQ over the loaded rows, without an error: a value set Union or Remove over NULL in a projection, and RangeLowerBoundComparer in a query. Each has a workaround, and its fix comes in a later 10.x minor (#397 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/397), EF Core (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/release/10.x/docs/efcore.md#known-differences-in-1020)).
- Docs: an ordering by LowerBound() or UpperBound() puts the empty rows and those without that bound where PostgreSQL puts NULLs, last ascending and first descending, also on a required column. A != null key in front sorts them as memory does (EF Core (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#read-bounds-merge-ranges-and-aggregate)).
- EF Core: on EF Core 10.0.2 and later, an empty captured list of ranges or sets keeps EF Core's ordering column (_ord, behind FirstOrDefault) an integer, as with elements. It was cast to the range or set type. No answer changed, but comparing it with an integer would have failed for the empty list (Captured lists (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#match-a-column-against-a-list-of-sets)).
Full changelog: https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/v10.2.0/src/CodoMetis.ValueRanges.EFCore.PostgreSQL/CHANGELOG.md