Npgsql AddWithValue doesn't work with transaction

Question

I am attempting to execute a query with a transaction in Npgsql as it make the code significantly cleaner and more consistent with queries in other systems with pure SQL. However I get the error Npgsql.PostgresException: 42703: column "_hash" does not exist on the following code.

var cmd = new NpgsqlCommand(@"
do
$do$
begin
    if ((select count(1) from components where hash = @_hash) = 0) then
        insert into components (hash, name) values (@_hash, @_name);
    end if;
end
$do$", db); // db is NpgsqlConnection connection
cmd.Parameters.AddWithValue("_hash", "00000000-0000-0000-0000-000000000000");
cmd.Parameters.AddWithValue("_name", "t_test");
cmd.ExecuteNonQuery(); // error on this line

The following does work for some reason which make me think that it is an issue with AddWithValue in Transactions

Hard coding the values;

var cmd = new NpgsqlCommand(@"
do
$do$
begin
    if ((select count(1) from components where hash = '00000000-0000-0000-0000-000000000000') = 0) then
        insert into components (hash, name) values ('00000000-0000-0000-0000-000000000000', 't_test');
    end if;
end
$do$", db);
cmd.ExecuteNonQuery();

Getting rid of the transaction

var cmd = new NpgsqlCommand("insert into components (hash, name) values (@_hash, @_name);", db)
cmd.Parameters.AddWithValue("_hash", "00000000-0000-0000-0000-000000000000");
cmd.Parameters.AddWithValue("_name", "t_test");
cmd.ExecuteNonQuery();

What is causing this issue and how can it be fixed?

NOTE: I can run the query which is failing in a database manager like JetBrains DataGrip so the query is not malformed.

Answer 1

You can't pass parameters to an anonymous do block. It is not related to npgsql but to Postgres.

The doc says:

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.