Query: Allow store types and type facets to be set explicitly (EF.StoreType)

[smitpatel]

#4937 introduced logic to infer Unicode-ness for literals when comparing to column. It works well for simple cases when one side of comparison is ColumnExpression. Though there are complex cases in which we are not inferring the information correctly atm. For e.g.

1. If one side of binary expression is subquery selecting one element of a column then the other side should have same facets as the column being projected in subquery.
2. Operators like concat which return string type values should pass uniform information towards parent because the siblings of concat should have same unicode-ness as concat. (See test Non_unicode_string_literals_is_used_for_non_unicode_column_with_concat)

For cases like above, we can improve logic to propagate information. If it is too hard to do then we should add functions like AsUnicode & AsNonUnicode.

[smitpatel]

Also cases of SqlFunction like CharIndex (comes from String.Contains method) the argument should have same type mapping. Though That could be different for individual functions.

[ajcvickers]

Note: when designing/implementing this, consider the case for parameters passed to FromSql, etc. See #8895
Added comment to that issue.

[ajcvickers]

See also comments in #11921

[ajcvickers]

See also comments in the thread here: #6717 (comment)

[roji]

Another issue which shows the need for a way to explicitly specify a store type: npgsql/efcore.pg#561.

[roji]

Nothing you're saying is stupid, but it's not exactly right either :)

DbType is a database-independent ADO.NET enum property on DbParameter. If works great if you want to use some universal type like int or string, which all databases support. However, once you need to use any type that's database-specific, the DbType enum isn't sufficient anymore. This is why each ADO.NET provider has its own enum (SqlDbType, NpgsqlDbType...) to allow specifying database-specific types.

The Npgsql EF Core provider doesn't use DbType at all, because it doesn't need any sort of database portability (it works only with Npgsql/PostgreSQL anyway), so it only uses NpgsqlDbType. EF Core doesn't know about about NpgsqlDbType when logging, so all you get is DbType=Object.

To summarize, Npgsql always sends an explicit database type ("type OID" in PostgreSQL parlance) with every parameter it sends. The type OID sent can be controlled via DbType, NpgsqlDbType, DataTypeName, or you can just let Npgsql use its default by not specifying any of those. But the current implementation will always tell PostgreSQL exactly what the data type is, thus preventing inference/coercion.

You can read about DbType and NpgsqlDbType in the Npgsql basic usage docs.

Note that it's still possible for EF Core to do better inference, i.e. select a better mapping based on the query context, thus making Npgsql send a different (and more correct) data type OID.

[dpsenner]

Thanks @roji for your valuable insights. To me DbType=Object was an indicator that there's something fishy going on. I'm glad that I was wrong. Keep up your good work!

[smuthuvels]

I am using EF Core 2.0.3 and I am getting the same issue for Contains search. Do you have any fix for that?

[smitpatel]

blocked on #13192

[bricelam]

I added a mechanism for query translators to provide explicit type mappings for function arguments and results in PR #13648. This allows us to fix individual cases.

[roji]

Another possible need for this: setting facets in order to allow something to participate in a set operation (currently set operations over types with differing facets isn't supported, #19129).