Null value types using the positional parameter API - guidance

[erichiller]

I've been writing all my new work using the positional $1 style parameters and I've not found a good way of handling nullable values, particularly, it seems for value types.

Previously something like this would work:

short? value = someCondition ? 1 : null;

cmd.Parameter.Add( new NpgsqlParameter<short?>( "parameter_name", value );

However, with positional parameters there is no parameter name.

Some methods I've tried:

cmd.Parameter.Add( new NpgsqlParameter<short?>( TypedValue = someCondition ? 1 : null ); // Throws

Throws InvalidCastException

cmd.Parameter.Add( new NpgsqlParameter<short?> { TypedValue = null! } ); // confusingly OK

cmd.Parameter.Add( new NpgsqlParameter<short>( TypedValue = (short)(someCondition ? 1 : (short?)null!) ); // Ok, but ugly

I haven't found any tests that cover use of nulls with positional parameters, just named ones.

Using the boxing API (Value = or NpgsqlValue = ) is of course possible, but I'd like to avoid boxing.

Any guidance would be great, ideally it would be in the docs, and I'd be happy to submit a PR for the docs if helpful.

Exception message:

System.InvalidCastException
Cannot write a value of CLR type 'System.Nullable`1[System.Int16]' as database type 'smallint' for parameter '$1'.

Stack trace:

System.InvalidCastException
Cannot write a value of CLR type 'System.Nullable`1[System.Int16]' as database type 'smallint' for parameter '$1'.
   at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.ValidateAndGetLengthCustom[TAny](TAny value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter`1.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ProcessParameters(TypeMapper typeMapper, Boolean validateValues, CommandType commandType)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataSourceCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataSourceCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
// ...

Further technical details

Npgsql version: 7.0.4
PostgreSQL version: 15.2
Operating system: Linux

[roji]

It seems you are conflating positional parameters (with no name) and the generic NpgsqlParameter<T>: you can (and should) use positional parameters with the non-generic NpgsqlParameter just fine.

The problem of passing null when using the generic NpgsqlParameter<T> with .NET value types is tracked by npgsql/npgsql#3679.

[erichiller]

Which non-generic variant should be used? I've tried:

new NpgsqlParameter { NpgsqlDbType           = NpgsqlDbType.Double, NpgsqlValue = nullableDouble } ;
// System.InvalidCastException: Parameter  must be set

new NpgsqlParameter { NpgsqlValue = nullableDouble } ; 
// System.InvalidOperationException: Parameter '$5' must have either its NpgsqlDbType or its DataTypeName or its Value set

new NpgsqlParameter { Value = nullableDouble } ; 
// System.InvalidOperationException: Parameter '$5' must have either its NpgsqlDbType or its DataTypeName or its Value set

new NpgsqlParameter( nullableDouble ) ;
// Cannot resolve constructor 'NpgsqlParameter(System.Nullable<double>)', candidates are...

/* The only one I've gotten to work */
nullableDouble is {} nonNullableDouble ? new NpgsqlParameter{ NpgsqlValue = nonNullableDouble, NpgsqlDbType = NpgsqlDbType.Double } : new NpgsqlParameter{ Value = DBNull.Value } ;

[roji]

@erichiller at the moment, the last version is the only one that works: DBNull.Value is the way to represent a database null value.

This is an unfortunate situation which I hope to resolve, but it's important to think about all this in the context of the .NET data API (ADO.NET), and not just Npgsql. In the meantime, you can have an extension method which adds a non-generic parameter with DBNull.Value for a null value, and a generic one with the non-null value otherwise.

[erichiller]

@erichiller at the moment, the last version is the only one that works: DBNull.Value is the way to represent a database null value.

This is an unfortunate situation which I hope to resolve, but it's important to think about all this in the context of the .NET data API (ADO.NET), and not just Npgsql. In the meantime, you can have an extension method which adds a non-generic parameter with DBNull.Value for a null value, and a generic one with the non-null value otherwise.

Thanks for the information. It might be good to have this in the official docs. I would be happy to add if you'd like.