Array Type Mapping

PostgreSQL has the unique feature of supporting array data types. This allow you to conveniently and efficiently store several values in a single column, where in other database you'd typically resort to concatenating the values in a string or defining another table with a one-to-many relationship.

Note

Although PostgreSQL supports multidimensional arrays, these aren't yet supported by the EF Core provider.

Mapping arrays

Simply define a regular .NET array or List<> property:

public class Post
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string[] Tags { get; set; }
    public List<string> AlternativeTags { get; set; }
}

The provider will create text[] columns for the above two properties, and will properly detect changes in them - if you load an array and change one of its elements, calling SaveChanges will automatically update the row in the database accordingly.

Operation translation

The provider can also translate CLR array operations to the corresponding SQL operation; this allows you to efficiently work with arrays by evaluating operations in the database and avoids pulling all the data. The following table lists the range operations that currently get translated. If you run into a missing operation, please open an issue.

.NET	SQL
array[1]	array[1]
array.Length	cardinality(array)
array1.SequenceEqual(array2)	array1 = array2
array1.Contains(element))	element = ANY(array)
array.Any()	cardinality(array) > 0
array1.Any(i => array2.Contains(i))	array1 && array2
array1.All(i => array2.Contains(i))	array1 <@ array2
array.Any(s => EF.Functions.Like(string, s))	string = LIKE ANY (array)
array.Any(s => EF.Functions.ILike(string, s))	string = ILIKE ANY (array)
array.All(s => EF.Functions.Like(string, s))	string = LIKE ALL (array)
array.All(s => EF.Functions.ILike(string, s))	string = ILIKE ALL (array)