CodoMetis.ValueRanges.EFCore.PostgreSQL 10.2.0

Prefix Reserved
There is a newer version of this package available.
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
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" Version="10.2.0" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" Version="10.2.0" />
                    
Directory.Packages.props
<PackageReference Include="CodoMetis.ValueRanges.EFCore.PostgreSQL" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add CodoMetis.ValueRanges.EFCore.PostgreSQL --version 10.2.0
                    
#r "nuget: CodoMetis.ValueRanges.EFCore.PostgreSQL, 10.2.0"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package CodoMetis.ValueRanges.EFCore.PostgreSQL@10.2.0
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.2.0
                    
Install as a Cake Addin
#tool nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.2.0
                    
Install as a Cake Tool

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:

  • TimeRange needs two opt-ins. PostgreSQL has no built-in timerange. You call HasPostgresRange to create the type, and EnableUnmappedTypes on the data source. The EF Core guide shows both in TimeRange and the custom timerange type.
  • Float64Range and Float32Range need the same two opt-ins. Create the types with HasPostgresRange("float8range", "double precision"), without a subtype_diff, which overflows a GiST index over very large values, and HasPostgresRange("float4range", "real"). Rows holding NaN or 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 the YearMonth types 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 -infinity and infinity. So the document, a column and a parameter all hold the same value.
  • ExecuteUpdate of 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 scaffold maps range columns to NpgsqlRange<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:

  • Intersect runs as *.
  • Union and Except turn both sides into multiranges and run as + and -. That matches their RangeSet return type. So the union of two disjoint ranges comes back as a real two-element multirange, not an error.
  • The factories CreateFinite, CreateUnboundedStart and CreateUnboundedEnd run 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, int8range and daterange half-open, as [lower, upper). The model keeps them closed, as [lower, upper]. The package makes up for the difference. UpperBound() runs as upper(x) - 1, and UpperBoundInclusive() as NOT upper_inf(x) AND NOT isempty(x). Results from the database always equal the results in memory, checked against a live PostgreSQL.
  • LowerBound() and UpperBound() return T?. PostgreSQL's lower and upper return NULL when that side is unbounded or the range is empty. The in-memory methods return null in the same cases.
  • Aggregates over zero rows return NULL in SQL. That is standard PostgreSQL behaviour. In memory, RangeAgg() over nothing returns the empty set. RangeIntersectAgg() returns null in both places.
  • DateTimeRange bounds are stored as wall-clock time. They are written as timestamp with DateTimeKind.Unspecified. A UTC-kinded DateTime is reinterpreted as wall-clock time, not converted.
  • DateTimeOffsetRange bounds are stored in UTC. They are normalized to UTC for timestamptz. You keep the instant, but the original offset doesn't come back.
  • DateTime.MinValue and MaxValue are stored as -infinity and infinity. 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, so upper_inf stays false.

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:

  • Union is the one set operation whose SQL result isn't deduplicated and sorted. It runs as array_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, OrderBy and a value in ExecuteUpdate. 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 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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

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