Arrays and Ranges

This page documents the Npgsql EF Core provider's support for PostgreSQL-specific array and range data types. PostgreSQL provides native support for arrays (collections of values of the same type) and ranges (bounded intervals of values), which are mapped to corresponding .NET types.

---

PostgreSQL Array Support

PostgreSQL arrays are first-class column types that can store multiple values of the same type in a single column. The provider maps these to .NET collection types such as arrays, List<T>, and other IEnumerable<T> implementations.

Array Type Mappings

Array Type Mapping Flow

Sources: src/EFCore.PG/Storage/Internal/Mapping/NpgsqlTypeMapping.cs24-39 test/EFCore.PG.FunctionalTests/Query/ArrayListQueryTest.cs22-31 test/EFCore.PG.FunctionalTests/Query/ArrayArrayQueryTest.cs25-34

Supported Collection Types

The provider supports mapping primitive collections to PostgreSQL arrays. For example, int[] and List<int> both map to the integer[] store type.

.NET Type	PostgreSQL Type	Description
int[]	integer[]	Integer array
List<int>	integer[]	Integer list
string[]	text[]	Text array
byte[]	bytea	Byte array (mapped to bytea by default)
uint	xid	Mapped via NpgsqlUIntTypeMapping

Sources: test/EFCore.PG.FunctionalTests/Query/ArrayListQueryTest.cs40-48 test/EFCore.PG.FunctionalTests/Query/ArrayArrayQueryTest.cs41-49 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlUIntTypeMapping.cs19

Array Querying and Indexing

The provider translates .NET indexers to PostgreSQL array indexing. Note that PostgreSQL arrays are 1-based, so the provider automatically adds 1 to the .NET 0-based index during translation.

Sources: test/EFCore.PG.FunctionalTests/Query/ArrayListQueryTest.cs38-48 test/EFCore.PG.FunctionalTests/Query/ArrayArrayQueryTest.cs53-67

---

PostgreSQL Range Support

PostgreSQL range types represent intervals of values with defined boundaries. The provider maps these to the NpgsqlRange<T> structure.

Range Type Mapping Architecture

Range Mapping Entity Association

Sources: src/EFCore.PG/Storage/Internal/Mapping/NpgsqlRangeTypeMapping.cs13-33 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlRangeTypeMapping.cs39-45

Range Operations

The provider supports various range operations via extension methods in NpgsqlRangeDbFunctionsExtensions. These translate to PostgreSQL range operators:

C# Method	PostgreSQL Operator	Description
Contains(value)	@>	Range contains element
Contains(range)	@>	Range contains range
Overlaps(range)	&&	Ranges overlap
IsStrictlyLeftOf(range)	<<	Range is strictly left of
IsStrictlyRightOf(range)	>>	Range is strictly right of

Sources: src/EFCore.PG/Extensions/DbFunctionsExtensions/NpgsqlRangeDbFunctionsExtensions.cs25-117

Custom and User-Defined Ranges

PostgreSQL allows users to define their own range types. The provider supports these via NpgsqlRangeTypeMapping.CreatUserDefinedRangeMapping. For user-defined ranges, the NpgsqlParameter.DataTypeName is used instead of a built-in NpgsqlDbType.

Sources: src/EFCore.PG/Storage/Internal/Mapping/NpgsqlRangeTypeMapping.cs70-78 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlRangeTypeMapping.cs139-158

---

Multirange Support (PostgreSQL 14+)

PostgreSQL 14 introduced multiranges, which are sets of non-overlapping ranges.

Multirange Operations

Multiranges support similar operations to ranges, translated via NpgsqlMultirangeDbFunctionsExtensions.

C# Method	PostgreSQL Operator
Contains(value)	@>
Overlaps(multirange)	&&
Merge()	range_merge()

Sources: src/EFCore.PG/Extensions/DbFunctionsExtensions/NpgsqlMultirangeDbFunctionsExtensions.cs25-80

---

Internal Implementation Details

Design-Time and Runtime Code Generation

During the scaffolding or compiled model generation process, the provider uses NpgsqlCSharpRuntimeAnnotationCodeGenerator to generate the code necessary to recreate type mappings at runtime.

Type Mapping Tweak Flow

Sources: src/EFCore.PG/Design/Internal/NpgsqlCSharpRuntimeAnnotationCodeGenerator.cs41-60 src/EFCore.PG/Design/Internal/NpgsqlCSharpRuntimeAnnotationCodeGenerator.cs113-165

Hstore Support

While technically a key-value store, hstore behaves similarly to a collection and is mapped to Dictionary<string, string> or ImmutableDictionary<string, string>.

Sources: src/EFCore.PG/Storage/Internal/Mapping/NpgsqlHstoreTypeMapping.cs13-38 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlHstoreTypeMapping.cs66-90

Aggregate Translations

The provider translates LINQ aggregate methods like Sum or Average over collections, as well as PostgreSQL-specific aggregates like array_agg.

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlMiscAggregateMethodTranslator.cs87-117 test/EFCore.PG.FunctionalTests/Query/GearsOfWarQueryNpgsqlTest.cs194-203