Query the length of a bytea value from PostgreSQL with EF Core

Question

In my .NET Core application with Entity Framework Core and Npgsql, I'd like to query records that contain binary data. But sometimes I don't need the actual data but just its length in bytes. That would be something like this query:

dbContext.BinaryFiles
    .Select(f => new
    {
        Name = f.Name,
        Length = f.Data.Length
    }
    .ToList();

The file class looks like this:

public class BinaryFile
{
    public string Name { get; set; }
    public byte[] Data { get; set; }
}

DbContext contains a DbSet<BinaryFile>. The table is defined as:

create table binary_files
(
    id serial primary key,
    name varchar(200) not null,
    data bytea not null
);

When I try this, I get the error: Npgsql.PostgresException: "42883: Function cardinality(bytea) does not exist". The generated query is this:

SELECT b.name AS "Name", cardinality(b.data) AS "Length"
FROM binary_files AS b

When I try this query interactively instead, I get a result:

SELECT b.name AS "Name", LENGTH(b.data) AS "Length"
FROM binary_files AS b

So the problem is that the .NET Array.Length property isn't translated to the adequate SQL LENGTH function but something else instead.

Is this a bug or am I using it incorrectly? How could I get the desired data only?

Versions:

• .NET Core 3.1.1
• Entity Framework Core 3.1.1
• Npgsql 4.1.2
• Npgsql.EntityFrameworkCore.PostgreSQL 3.1.0
• PostgreSQL 12 on Windows 10 x64

Answer 1

Translating Length on byte arrays hasn't been implemented yet... Using https://github.com/npgsql/efcore.pg/issues/1226 tracks this. The EF Core main providers (SqlServer and Sqlite) recently merged support for this for 5.0 only as well.