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
                    
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.4.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.4.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.4.0
                    
#r "nuget: CodoMetis.ValueRanges.EFCore.PostgreSQL, 10.4.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.4.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.4.0
                    
Install as a Cake Addin
#tool nuget:?package=CodoMetis.ValueRanges.EFCore.PostgreSQL&version=10.4.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.

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, Intersect and Except leave their SQL result unsorted. PostgreSQL sorts text by the database collation rather than ordinally, so it can't sort it the way your sets are, and Union runs as array_cat, which repeats an element both sets hold.
  • Most uses don't depend on that. The operators @>, <@ and && ignore order and repeats. Count counts 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, Contains over a list of several sets and a value in ExecuteUpdate. 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 by OrderId.CompareTo in 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 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

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