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

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.

Is there a reason you are not using LINQ for the queries?It's easier, widely used, and optimized for most of the queries you would need.It would look something like this, provided you create a Product entity:

DbContext.Products.Add(new Product() { ProductName = "Test Product", Weight = 5, ... }await DbContext.SaveChangesAsync();

Then if you want to retrieve product with id productId:

Product product = await DbContext.Products.FirstOrDefaultAsync(p => p.Id == productId);

You need all deleted products?

List<Product> deletedProducts = await DbContext.Products.Where(p => p.DeletionTime != null).ToListAsync();

(Those examples rely on you adding the needed properties to the Product entity, like Id and DeletionTime)