How to deal with a text[] array field type in Npgsql?

Question

I have a Postgres 12 database with a single table:

CREATE TABLE public.messages
(
    sender text COLLATE pg_catalog."default",
    "timestamp" timestamp with time zone,
    message_id bigint,
    text text COLLATE pg_catalog."default",
    priority bigint,
    parameters text[] COLLATE pg_catalog."default"
)

Now, when I want to use this table in met .NET Winforms (not .NET Core) application by right-clicking the project, add new item, ADO.NET Entitity Data Model, EF Code First from Database, configuring my connection (successful connection test), choosing my table, I get this error:

Severity Code Description Project File Line Suppression State Warning Error 6005: The data type '_text' is currently not supported for the target Entity Framework version; the column 'parameters' in the table 'test.public.messages' was excluded. LogTest D:\Projekte\LogTest\Model1.cs 1

The parameters field did not make it into the messages class:

[Table("public.messages")]
public partial class message
{
    public long id { get; set; }
    public string sender { get; set; }
    public DateTimeOffset? timestamp { get; set; }
    public long? base_message_id { get; set; }
    public string text { get; set; }
    public long? priority { get; set; }
}

Apart from parameters, the model works and I can get data from the DB. When I try to add the property manually, it still runs, but parameters is always null:

    public string[] parameters { get; set; } // also tried List<string>

How can I make it work? From what I understand when reading the docs, I think it should be possible?

My application is targetting .NET Framework 4.6, I'm using EntityFramework6.Npgsql version 6.4.1.0 with Npgsql version 4.0.10.0.

Answer 1

Mapping arrays is not supporting in EF6, and will probably never be - EF6's type model is very closed/restricted, and doesn't allow for exposing database-specific types (and especially not complicated cases like arrays).

The EF Core provider does have pretty good support for PostgreSQL arrays, if they're central to what you're doing I'd suggest considering Core instead.