Get value from sql select if parameter may be null

Question

Looking for a method to get value of first row in first column from arbitary sql query in ASP.NET MVC Core application using EF Core.

Like

   var total = ctx.ExecuteScalar<decimal?>(@"select ... where p1={0}", null);

Tried https://github.com/weitzhandler code from https://github.com/dotnet/EntityFramework.Docs/issues/969 with Npgsql EF Provider (changed to synchronous):

public partial class EevaContext : DbContext
{   
    public T ExecuteScalar<T>(string rawSql, params object[] parameters)
    {  
        var context = this;
        var conn = context.Database.GetDbConnection();
        using var command = conn.CreateCommand();
        command.CommandText = rawSql;
        if (parameters != null)
            foreach (var p in parameters)
                command.Parameters.Add(p ?? DbNull.Value);
        conn.Open();
        return (T)command.ExecuteScalar();
    }

If parameter value is null line

command.Parameters.Add(p ?? DbNull.Value);

throws error

InvalidCastException: The value "" is not of type "NpgsqlParameter" and cannot be used in this parameter collection. Npgsql.NpgsqlParameterCollection.Cast(object value)

Which is best way to run such query ? Maybe EF core has some builtin method? Or is it better to use some type and inject it into EF Context is OnModelCreated method ?

If there is no better way how to fix this code so that parameters with null values are also accepted ?

There are lot of such queries with lot of parameters in application and re-qriting all of them in huge work. WebMatrix QueryValue was used in .NET 4 which allows null values in parameters.

score 1 · Accepted Answer · answered Jan 17 '21 at 17:28

command.Parameters.Add(p ?? DbNull.Value);

Is p an NpgsqlParameter, or some value that you want to send?? You cannot simply add values (or null) to command.Parameters.Add - you need to either wrap these with NpgsqlParameter or use command.Parameters.AddWithValue() instead. Note that the NpgsqlParameter also needs to have its ParameterName set correctly, correponding to the placeholders in your raw SQL (e.g. @param1).

Get value from sql select if parameter may be null

1 Answers1