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
