CodoMetis.ValueRanges.EFCore.PostgreSQL
10.4.0
Prefix Reserved
dotnet add package CodoMetis.ValueRanges.EFCore.PostgreSQL --version 10.4.0
NuGet\Install-Package CodoMetis.ValueRanges.EFCore.PostgreSQL -Version 10.4.0
<PackageReference Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" Version="10.4.0" />
<PackageVersion Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" Version="10.4.0" />
<PackageReference Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" />
paket add CodoMetis.ValueRanges.EFCore.PostgreSQL --version 10.4.0
#r "nuget: CodoMetis.ValueRanges.EFCore.PostgreSQL, 10.4.0"
#:package CodoMetis.ValueRanges.EFCore.PostgreSQL@10.4.0
#addin nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.4.0
#tool nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.4.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.
Union, Add, Intersect and Except run as SQL too, on every set type except the two text
ones, and give the deduplicated, sorted set memory gives:
orders.Where(o => o.Codes.Union(more) == expected); // ARRAY((SELECT DISTINCT ON (vr0.x) vr0.x FROM unnest(…) …)) = @expected
orders.Where(o => o.Codes.Intersect(wanted).Count > 1);
Good to know:
- On text,
Union,Add,IntersectandExceptleave their SQL result unsorted. PostgreSQL sortstextby the database collation rather than ordinally, so it can't sort it the way your sets are, andUnionruns asarray_cat, which repeats an element both sets hold. - Most uses don't depend on that. The operators
@>,<@and&&ignore order and repeats.Countcounts the distinct elements, and==compares by mutual containment. When you load the result, reading the set sorts and deduplicates it again. - Anything that depends on order or repeats throws instead. That covers
GroupBy,Distinct,OrderBy,Containsover a list of several sets and a value inExecuteUpdate. The query throws rather than return a different answer from memory. The package checks this on the translated SQL. - Your own element types must sort as their underlying type does. An
Int32Set<OrderId>sorts the stored integers in the database and byOrderId.CompareToin memory. Vogen and StronglyTypedId compare by the underlying value.
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 |
|---|---|
Length on the range types other than Int32Range and Int64Range |
not translated: 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 |
LocalDateRange.Month, Quarter, Year and IsoWeek, and YearMonthRange.Quarter and Year, with an argument read from a column |
not translated: PostgreSQL has no dates before 4714-11-24 BC, so the month and the year that hold its first day have no start, with Npgsql's infinity conversions on or off. Built from values your code passes in, the range is sent as one constant, so comparing a column against a month runs as SQL. On DateRange they run 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 a DateTimeRange, with a DateTime stored as timestamp with time zone |
not translated: GREATEST and LEAST would compare timestamp and timestamptz through the session's time zone. Map the property as timestamp without time zone and it runs as SQL |
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
You can read every guide, with an API reference, on the documentation site.
- The EF Core guide shows every translation with the SQL it generates.
- Getting started walks you through a first entity, migration and query.
- The walkthrough follows one booking through a minimal API and PostgreSQL, and shows what changes at each step.
- 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.4.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 |
Some calls answer differently: Changed answers in 10.4.0 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1040).
Changed answers
- EF Core: a StringSet or StringSet<TElement> property with a collation that may not compare bytes throws when the model is built, and so does EF.Functions.Collate over one in a query. Under a case-insensitive collation, Contains("a") matched {A} and Remove("a") deleted A, unlike in memory (#496 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/496), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1040)).
- EF Core: a query that compares a StringSet or StringSet<TElement> with a value whose column has a collation that may not compare bytes, or with EF.Functions.Collate(value, …) under one, throws, because the database compares under it: Contains(g.Owner) matched {A} for "a" on a case-insensitive Owner, and Remove(g.Owner) in ExecuteUpdate deleted A (#512 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/512), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1040)).
- EF Core: on a DateRange and its multirange, LowerBound(), UpperBound(), LowerBoundInclusive() and Clamp read an exclusive infinity or -infinity in a query as the row loads. On [2026-01-01,infinity), UpperBound() returned 9999-12-31 where memory gives 9999-12-30, and on (-infinity,2026-01-01) LowerBound() returned 0001-01-01 (#502 (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/issues/502), Migration (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/migration.md#changed-answers-in-1040)).
Added
- EF Core: Clamp runs as SQL on every range type, no longer only on Int32Range, Int64Range and DecimalRange, and answers as memory does, at the extremes too. A DateTime stored as timestamp with time zone against a DateTimeRange runs in memory, because PostgreSQL would compare them through the session's time zone (What runs where (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#what-runs-where)).
- EF Core: Union, Add, Intersect and Except on value sets run as SQL for every set type except StringSet and StringSet<TElement>. They give the deduplicated, sorted set memory gives, so a query can compare, count, group by and store the result (What runs where (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#what-runs-where)).
- EF Core: on StringSet and StringSet<TElement>, Intersect, Except and Add run as SQL, left unsorted, where order and repeats don't change the answer. Count over them and over Union counts the distinct elements, and == and != compare by mutual containment. Ordering, grouping and storing one still throw (Combine sets with Union (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#combine-sets-with-union)).
- EF Core: Count on a RangeSet runs as SQL, so a query can filter and sort by how many separate ranges a multirange holds. It counts the ranges memory holds, and a null multirange gives null. Near the element type's extremes, a multirange the database computes can count one range more (What runs where (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/efcore.md#what-runs-where)).
Changed
- Docs: a value set over your own element type, other than StringSet<TElement>, needs a CompareTo that orders values as the underlying type does, because the database sorts the underlying values (Value sets (https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/main/docs/value-sets.md#how-your-element-types-sort-and-serialize)).
Full changelog: https://github.com/CaffeinatedCoder/CodoMetis.ValueRanges/blob/v10.4.0/src/CodoMetis.ValueRanges.EFCore.PostgreSQL/CHANGELOG.md