npgsql.EntityFrameworkCore.PostgreSQL - execute function with json parameter is not working

Question

Database: Postgresql

ORM: Entity framework core with Npgsql.EntityFrameworkCore.PostgreSQL

Tried to call below fuction passing product list as json

dbContext.product.FromSqlRaw("SELECT pa.usp_set_product({0})", productjson).ToList();

return error as : "42883: function pa.usp_set_product(text) does not exist"

Then tried with below

dbContext.product.FromSqlRaw(@"SELECT pa.usp_set_product('" + productjson+ "')").ToList();

returns error: " Input string was not in a correct format "

Then tried below which works fine

 using (var cmd = new NpgsqlCommand(@"SELECT pa.usp_set_product(@productjson)", conn))
   {
    cmd.Parameters.Add(new NpgsqlParameter("productjson", NpgsqlDbType.Json) { Value = productjson});
    cmd.ExecuteNonQuery();
   }

Any idea, please-

1. why FromSqlRaw with JSON parameter is not working
2. is there any drawback using NpgsqlCommand - does it support Linux-container(docker)

Thanks,

@paul

Answer 1

When you include a parameter value in FromSqlRaw, it will be sent as the default corresponding PG type; I'm assuming that your productjson is a .NET string, which maps to a PG text, not json. The result is the same as your lower-level code using NpgsqlCommand, but without specifying NpgsqlDbType.Json.

EF Core also allows you to pass a DbParameter instance instead of a raw value, so you should be able to do the following:

var jsonParam = new NpgsqlParameter("productjson", NpgsqlDbType.Json);

dbContext.product.FromSqlRaw("SELECT pa.usp_set_product(@jsonParam)", jsonParam).ToList();

More information is available on this doc page.

Note: never concatenate or interpolate a string into the SQL of FromSqlRaw - that is vulnerable to SQL injection. Carefully read the EF docs for more information.

Note2: consider using the PostgreSQL jsonb type instead of json. You can read about the differences here.