Port to 3.0.0-preview7 - #943

Is something else responsible for generating the prefix now?

Great question :)

One of the great new features of the 3.0 query pipeline is type inference. Previously, EF Core used to find the default type mapping for a given CLR type, and use that to generate both literals and parameters. This is what caused all the issues we saw with citext, as a citext column was compared with a string parameter, and we'd send the parameter as text and PostgreSQL would perform case-sensitive comparison.

With the new pipeline, the type mappings of parameters and literals are inferred from their context: if you compare a citext column to a string literal or parameter, the column's type mapping (citext) will be applied to the other side, and the parameter (and literal) will be generated correctly. That's a great improvement.

Now after some thought, I'm pretty sure that in most cases we don't actually need typed literals, as PostgreSQL will infer their types based on context as well. This isn't related to the new EF inference feature - it just made me think about this again. Do you see any scenario where JSONB has to be explicitly specified in order for PostgreSQL to correctly understand expression?

/cc @smitpatel

Do you see any scenario where JSONB has to be explicitly specified in order for PostgreSQL to correctly understand expression?

My initial thoughts were about JSON vs JSONB inference, and how that might impact function/operator resolution (and maybe trivial round-tripping?).

But perhaps the new pipeline still delegates to the provider for type resolution and/or casting when it comes to custom operators?

austindrenski=> SELECT JSONB '{"a": {"b":{"c": "foo"}}}' #> '{a,b}';

   ?column?   
--------------
 {"c": "foo"}
(1 row)

austindrenski=> SELECT '{"a": {"b":{"c": "foo"}}}' #> '{a,b}';

ERROR:  operator is not unique: unknown #> unknown
LINE 1: select '{"a": {"b":{"c": "foo"}}}' #> '{a,b}';
                                           ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Good discussion - but it helps to think first about the C# LINQ side which would produce the SQL. Specifically, how would you go about producing the SQL above with LINQ? The C# counterpart to jsonb is a string, but a string literal by default gets mapped to text, not jsonb.

More generally, the SQL you wrote above involves two literals - that's quite an artificial scenario that may not always be fully supported. The generic logic with inference is that somewhere in your expression we're going to have a column, which has a clear and definite type mapping (json, jsonb, citext, whatever). As that column is combined in expressions (binary, functions, whatever), its type mapping is applied to the other side(s) of the expression, and so the type mapping bubbles up as necessary. In your example there's only literals, so no way of knowing about any specific type mappings.

@smitpatel may have additional thoughts on this. Do you think there's a good reason to provide explicit typing for literals of type mappings which aren't the default for their CLR type?