Parameterization in PostgreSQL queries

[murkykiska]

I encountered the following problem:
If there is a “where” or “limit” in queries for postgres, the following queries are built:

int number = 3;       
var users1 = connection.GetTable<User>().Where(u => u.Id > number);

--  PostgreSQL.15 PostgreSQL
DECLARE @number Integer -- Int32
SET     @number = 3

SELECT
        u."Id",
        u."Name",
        u."Email",
        u."CreatedAt"
FROM
        "User" u
WHERE
        u."Id" > :number

Such a request is not correct in syntax, i.e. when we try to execute it we get the following error: Npgsql.PostgresException: "42601: Syntax error (approximate position: "@")

A workaround was found: if you substitute a specific number/string into a linq query, then they are directly substituted into the “where”. But in real tasks, more complex data types are used (guides/dates, etc.), and this way does not work with them.

var sql = service.BuildQuery(dc =>
{   
    return
        dc.Table
            .AsSqlServer().WithNoLock()
          .Where(r => r.InstanceID == Guid.Empty)
          .OrderBy(r => r.Number)
          .Take(10);
});

--  PostgreSQL.9.2 PostgreSQL
DECLARE @Empty Uuid -- Guid
SET     @Empty = '00000000-0000-0000-0000-000000000000'::uuid
DECLARE @take Integer -- Int32
SET     @take = 10

SELECT
	"r"."RowID",	
    "r"."Number",
	"r"."InstanceID"	
FROM
	"Table" "r"
WHERE
	"r"."InstanceID" = :Empty
ORDER BY
	"r"."Number"
LIMIT :take

Is it possible to get rid of variable declarations?

Environment details

Linq To DB version: 5.4.1

Database (with version): PostgreSQL 15

ADO.NET Provider (with version): Npgsql

Operating system: Windows 11

.NET Version: .net framework 4.8, .net 6.0

[sdanyliv]

Currently linq2db is not usable for building just SQL, what you see is debug information, which executes only in SQL Server. What is the implication of generating SQL queries without execution?