Query string and DbCommand support

[roji]

See dotnet/efcore#6482, dotnet/efcore#19335, dotnet/efcore#19368. Specifically look into producing SQL that can run out of the box (with parameter syntax etc, not trivial).

Also look at tests which have CanExecuteQueryString on their fixture (e.g. SpatialQuerySqlServerFixture, TPTGearsOfWarQueryNpgsqlTest)

[roji]

Did some research on this, and I don't see any (sane) way to implement this at the moment - punting this for 5.0. tl;dr PostgreSQL doesn't support defining variables and using them in queries (outside of PL/pgSQL).

The following is probably the most correct way to do this in PG:

PREPARE fooplan AS SELECT $1;
EXECUTE fooplan('Hunter Valley');
DEALLOCATE fooplan;

However, our query SQL currently uses @p for parameter placeholders, and the ADO layer translates that to the PostgreSQL-native $1. So the command text as generated by the EF pipeline contains wrong placeholders. If we implement "raw mode" in Npgsql (npgsql/npgsql#1042), we'd be able to modify the EF provider to use that and generate PostgreSQL native placeholders, at which point the above becomes possible.

(However, investigate split queries too...)

/cc @ajcvickers in case you're interested.

Failed alternatives

Anonymous DO block

DO blocks can't execute queries (and return the data).

Customized options

Another possible way to implement parameters in SQL is with customized options:

SET __ef.foo = 'bar';
SELECT current_setting('__ef.foo');

The problem here is that we can't change the parameter name/placeholder (the thing embedded in the query), but this technique would require that (we need to do current_setting).

One-time function

With function we can actually have @p placeholders as they currently appear in the current SQL:

CREATE OR REPLACE FUNCTION add(i integer, j integer) RETURNS int
AS 'select @i + @j;' LANGUAGE SQL;

However, functions must have a defined return type, which we don't have (including polymorphic functions, where the return type depends on the type of a parameter).

CTEs

Theoretically can also do CTEs, but that's likely to raise all kinds of issues:

with const as (
    select 1 as val
)
select . . .
from const cross join
     <more tables>

As with customized options, we can't change the parameter placeholder inside the query, so this doesn't work.

[roji]

Note: for now, we can go with properly-typed literals instead of parameters like in the SQL Server case (see discussion dotnet/efcore#19368 (comment)). This isn't identical to generating parameters (which is what EF would do), but for PG it seems at least better than not supporting this at all.

[roji]

Generating a query string with literals is going to be difficult here... The input to query string generation is a fully-generated CommandText and its parameters; integrating the literals into the text at this point would mean parsing it, identifying parameter placeholders and replacing them with the literal representations of their corresponding parameter values. We have a lexical parser to do this kind of thing within Npgsql (ADO), but I don't want to bring this into EF Core for now.

We can reconsider this later based on demand/votes. For now, the generated query string will have the parameters in comments, so it's pretty easy to manually tweak the text and replace the placeholders when necessary.

[roji]

dotnet/efcore#27377 will allow us to switch to positional parameter placeholders ($1, $2) in Npgsql, at which point the PREPARE/EXECUTE/DEALLOCATE option above becomes possible.