Why is NpgsqlParameter not working when passed to DbSet.FromSqlRaw

user_5692675 0
Viewed 1635

As i mentioned in title, DbSet.FromSqlRaw not working when I pass NpgsqlParameter. I always get null.

Here are some variations i have tried:

long value = 3;
NpgsqlParameter npgsqlParameter = new NpgsqlParameter("value", value);
string query = "SELECT * FROM \"Admin\".\"User\" as usr WHERE \"usr\".\"Value\" = '@value'";

var _user = _AdminPanelDbContext
           .User
           .FromSqlRaw(query, npgsqlParameter)
           .FirstOrDefault();

long value = 3;
NpgsqlParameter npgsqlParameter = new NpgsqlParameter("value", value);
string query = "SELECT * FROM \"Admin\".\"User\" as usr WHERE \"usr\".\"Value\" = ':value'";

var _user = _AdminPanelDbContext
           .User
           .FromSqlRaw(query, npgsqlParameter)
           .FirstOrDefault();

Before someone asks "Why don't you use inline interpolated strings?" like this.

FromSqlRaw($"SELECT * FROM \"Admin\".\"User\" as usr WHERE \"usr\".\"Value\" = {value}");

First reason is, because I'm reusing that sql query (to be more precise, saving it to audit) and second reason is that I'm aware that NpgsqlParameter is also SQL injection safe.

npgsql asp.net-core-webapi postgresql c#
1 Answers

Sorry, I can't check the exact syntax right now - but how about something like:

    long value = 3;
    var searchParam = new NpgsqlParameter("value", value);
    var _user = _AdminPanelDbContext.User.FromSqlRaw("select * from admin.user as usr where usr.value = @searchParam;", searchParam).FirstOrDefault();

There's an end-to-end example here.

However, sounds like you're using Postgres. If yes, explains why you're seeing the error about needing to use NpgsqlParameter instead of SqlParameter.

user_16574270
Related