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; all these translations work both for .NET arrays (int[]) and for generic Lists (List<int>). If you run into a missing operation, please open an issue.
| .NET | SQL | Notes |
|---|---|---|
| array[0] | array[1] | |
| array.Length / list.Count | cardinality(array) | |
| array.Skip(2) | array[3,] | Added in 8.0 |
| array.Take(2) | array[,2] | Added in 8.0 |
| array.Skip(1).Take(2) | array[2,3] | Added in 8.0 |
| array1 == array2 | array1 = array2 | |
| array1.SequenceEqual(array2) | array1 = array2 | |
| arrayNonColumn.Contains(element) | element = ANY(arrayNonColumn) | Can use regular index |
| arrayColumn.Contains(element) | arrayColumn @> ARRAY[element] | Can use GIN index |
| array.Append(element) | array_append(array, element) | Added in 6.0 |
| array.Where(i => i != 8) | array_remove(array, value) | Added in 9.0 |
| array1.Concat(array2) | array1 || array2 | Added in 6.0 |
| array.IndexOf(element) | array_position(array, element) - 1 | Added in 6.0 |
| array.IndexOf(element, startIndex) | array_position(array, element, startIndex + 1) - 1 | Added in 6.0 |
| String.Join(separator, array) | array_to_string(array, separator, '') | Added in 6.0 |
| array.Any() | cardinality(array) > 0 | |
| array1.Intersect(array2).Any() | array1 && array2 | Added in 8.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) | |
| EF.Functions.ArrayAgg(values) | array_agg(values) | Added in 7.0, See Aggregate functions. |