Fully support multi-dimensional arrays

[roji]

Single-dimensional PostgreSQL arrays are well-supported, but not multi-dimensional ones. This issue is meant to see how many people actually want this - please upvote if you'd benefit from it.

Things that need to be implemented:

• Literal value generation
• ValueComparer (comparison and snapshotting)
• PostgresArrayIndexExpression needs to have multiple indexes, just like .NET ArrayIndexExpression
• Full querability as a primitive collection?

[alexzaytsev-newsroomly]

@roji The documentation here: http://www.npgsql.org/efcore/mapping-and-translation.html says However, operations such as indexing the array, searching for elements in it, etc. aren't yet translated to SQL and will be evaluated client-side. This will probably be fixed in 1.2. Since the latest version is over 2.0, i wanted to check - is something like

context.MyEntities.FirstOrDefault(x => x.ArrayProperty.Any(i => i == 42)) expected to be evaluated client side or actually sent to the backend?

(i noticed that Contains() is listed as supported on the same page)

[roji]

Will fix the docs, thanks. Yes, array operation translation was added to 2.0, which means server-evaluation (see #120). The translation list on the docs page is correct, I'll remove the old sentence you spotted. If you run into any issues or client evaluation occurs, please open a new issue.

Note, however, multidimensional arrays (this issue) aren't supported at all as well as single-dimensional ones.

[krzlabrdx]

@roji It seems that multidimensional arrays are essentially a dead feature but is it possible to somehow work this missing feature around for very limited use cases?

I'm using EF Core just to prepare db migration, read the data with no LINQ expressions and I store them with no EF Core at all using COPY operation:

[Table("abcs")]
class Abc
{
  // ...

  // fails to generate migration, maybe we'd have to use some kind of custom converter?
  [Column("my_col", TypeName="bigint[][]")]
  public long[][] MyCol { get; set; } // or ideally List<List<long>>
}

// reading
var someData = await _dbContext.Abcs.FromSqlInterpolated("...").ToListAsync();

// writing
var _conn = (NpgsqlConnection)dbContext.Database.GetDbConnection()

await using var writer = await _connection.BeginBinaryImportAsync("COPY abcs (..., ..., my_col, ..) FROM STDIN (FORMAT BINARY)", token);

foreach (...) {
  await writer.StartRowAsync(token);
  await writer.WriteAsync(..., NpgsqlDbType.Bigint, token);
  await writer.WriteAsync(abc.MyCol.ToArray(), NpgsqlDbType.Array | NpgsqlDbType.Bigint, token); // how to specify array of arrays?
}

await writer.CompleteAsync(token);

[roji]

It seems that multidimensional arrays are essentially a dead feature

Multidimensional array support in the EF provider isn't dead - it's just not fully supported, and not enough people have asked for improvements to it, so I haven't prioritized working on it.

Your above question has nothing to with EF - the COPY API works at the (non-EF) ADO.NET layer, where multidimensional arrays should be fully supported. The NpgsqlDbType for a multidimensional array is the same as for a regular array, since in PostgreSQL there's just one array type, regardless of dimensions.

So the code above looks like it should work; if it doesn't, please open an issue in https://github.com/npgsql/npgsql with a full code repro so we can investigate.

[Rincho-M]

Are there any plans to implement this feature, or is it the same as it was five years ago?

[roji]

@Rincho-M so far this hasn't received enough user feedback to warrant spending the time on it. What exact capability are you missing here?

[Rincho-M]

@roji I want to write and read a model with a two-dimensional array property. It seems to save correctly, but it gives an index-out-of-range exception when I'm trying to read it

[roji]

@Rincho-M saving and loading is generally supposed to work: see the following minimal code sample which works for me with 7.0; if you're seeing something different, please open a new issue with a minimal repro. Anything beyond that may not work though.

Code sample

await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

ctx.Blogs.Add(new() { Matrix = new[,] { { 1, 2 }, { 3, 4 } } });
await ctx.SaveChangesAsync();

ctx.ChangeTracker.Clear();

var blog = await ctx.Blogs.SingleAsync();
foreach (var i in blog.Matrix)
{
    Console.WriteLine(i);
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int[,] Matrix { get; set; }
}

[Rincho-M]

@roji so I did some testing and it appears that it doesnt work only with char arrays. And not only with multidimensional arrays, but with one-dimentional ones too. I didnt find opened issue with this error so I will try to open one on this weekend

[MattParkerDev]

This appears to work with int[,] and double[,], at least with Postgres, but not enums, e.g. MyEnum[,]. Given that enums are just integers, is there an easy way to support this?

[roji]

Enums need to be value-converted internally, and the value converter that takes care of arrays doesn't support multidimensional ones yet.

[Pieeer1]

Is there a workaround outside of a custom json serialization? IE:

        way.Property(w => w.Nodes)
            .HasColumnName("nodes")
            .HasColumnType("jsonb")
            .HasConversion(
                v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
                v => JsonSerializer.Deserialize<long[][]>(v, (JsonSerializerOptions?)null)!
            );

[roji]

@Pieeer1 basic multidim array support is there - if all you want to do is serialize/deserialize (value converters aren't supported, possibly some other edge cases). So if all you want to do is serialize/deserialize (which is what your JSON value converter does), then you shouldn't need anything else.

Note that PG has a proper array type, rather than serializing arrays to JSON.

[Pieeer1]

I was getting some issues with a bigint[][] with the data type set, I can retry without the custom column type and see what I get with the migration.

[Pieeer1]

I was getting some issues with a bigint[][] with the data type set, I can retry without the custom column type and see what I get with the migration.

Well it would make sense why it wasn't liking it, apparently osm2pgsql stores the data in a bigint[][] but in reality it is actually just a singular dimensional big int array.