Cannot determine type of array parameter when its value is null

Question

Using PostgreSQL database and Dapper.

Here's a minimal example of the failing code:

await using var connection = new NpgsqlConnection(_configuration.ConnectionString);

return await connection.QueryAsync<Diamond>(
  @"SELECT
      id,
      name
    FROM
      product
    WHERE
      @Names IS NULL OR name = ANY(@Names);",
  new
  {
      Names = Names,
  });

This works fine if Names = new { "Tom", "Dick", "Harry" }.

However it fails if Names = (string[]) null.

The error I get is:

Npgsql.PostgresException (0x80004005): 42P18: could not determine data type of parameter $1
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 419

Is it possible to send nullable array parameters in Dapper to PostgreSQL, or do I need to find a workaround?

Quite similar problem is discussed over here (forums.devart.com/viewtopic.php?t=37803) but the postgre driver is a different one. — Peter Csala
– Peter Csala, Commented Apr 28, 2020 at 15:45

numberjak · Accepted Answer · 2020-05-01 19:47:42Z

3

@PeterCsala was correct. The issue was that PostgreSQL prepares the query server side, so casting the parameter in C# world doesn't help. The casting must be done in SQL world.

The working code becomes:

await using var connection = new NpgsqlConnection(_configuration.ConnectionString);

return await connection.QueryAsync<Diamond>(
  @"SELECT
      id,
      name
    FROM
      product
    WHERE
      @Names::text[] IS NULL OR name = ANY(@Names);",
  new
  {
      Names = Names,
  });

answered May 1, 2020 at 19:47

numberjak

1,3154 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Csala Over a year ago

Thanks for sharing the solution with the community. :)

Collectives™ on Stack Overflow

Cannot determine type of array parameter when its value is null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related