Ranges and Multiranges

PostgreSQL has the unique feature of supporting range data types. Ranges represent a range of numbers, dates or other data types, and allow you to easily query ranges which contain a value, perform set operations (e.g. query ranges which contain other ranges), and other similar operations. The range operations supported by PostgreSQL are listed in this page. The Npgsql EF Core provider allows you to seamlessly map PostgreSQL ranges, and even perform operations on them that get translated to SQL for server evaluation.

In addition, PostgreSQL 14 introduced multiranges, which are basically sorted arrays of non-overlapping ranges with set-theoretic operations defined over them. Most range operators also work on multiranges, and they have a few functions of their own. Multirange support in the EF Core provider was introduced in version 6.0.0.

Ranges

Npgsql maps PostgreSQL ranges to the generic CLR type NpgsqlRange<T>:

public class Event
{
    public int Id { get; set; }
    public string Name { get; set; }
    public NpgsqlRange<DateTime> Duration { get; set; }
}

This will create a column of type daterange in your database. You can similarly have properties of type NpgsqlRange<int>, NpgsqlRange<long>, etc.

User-defined ranges

PostgreSQL comes with 6 built-in ranges: int4range, int8range, numrange, tsrange, tstzrange, daterange; these can be used simply by adding the appropriate NpgsqlRange<T> property in your entities as shown above. You can also define your own range types over arbitrary types, and use those in EF Core as well.

To make the EF Core type mapper aware of your user-defined range, call the MapRange() method in your context's OnConfiguring() method as follows:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseNpgsql(
        "<connection_string>",
        options => options.MapRange<float>("floatrange"));

This allows you to have properties of type NpgsqlRange<float>, which will be mapped to PostgreSQL floatrange.

The above does not create the floatrange type for you. In order to do that, include the following in your context's OnModelCreating():

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.HasPostgresRange("floatrange", "real");

This will cause the appropriate CREATE TYPE ... AS RANGE statement to be generated in your migrations, ensuring that your range is created and ready for use. Note that HasPostgresRange() supports additional parameters as supported by PostgreSQL CREATE TYPE.

Multiranges

Note

This feature was introduced in version 6.0

Npgsql maps arrays or lists of NpgsqlRange<T> to PostgreSQL multiranges:

public class Store
{
    public int Id { get; set; }
    public string Name { get; set; }
    public NpgsqlRange<DateTime>[] OpeningTimes { get; set; }
}

Operation translation

Ranges can be queried via extensions methods on NpgsqlRange:

var events = context.Events.Where(p => p.Duration.Contains(someDate));

This will translate to an SQL operation using the PostgreSQL @> operator, evaluating at the server and saving you from transferring the entire Events table to the client. Note that you can (and probably should) create indexes to make this operation more efficient, see the PostgreSQL docs for more info.

The following table lists the range operations that currently get translated. Most operations work on both ranges and multiranges (starting with version 6.0.0); the multirange version is omitted for brevity.

If you run into a missing operation, please open an issue.