Allow expressing VALUES expressions

[roji]

PostgreSQL supports the following expression: VALUES (1, 'one'), (2, 'two'), (3, 'three'). This is a table-returning expression which can be used anywhere a table can (e.g. in subqueries, JOINs), and can be useful for specifying constant tables. Note that this syntax makes up the standard SQL INSERT statement (it's what comes after INSERT INTO foo (<columns>) ...).

As part of EF Core 7.0 we may add row value expressions (see dotnet/efcore#26822). If that's done, a PostgreSQL-specific ValuesExpression could be added, which is just a collection of RowValueExpressions.

/cc @smitpatel

Originally requested in https://stackoverflow.com/questions/70411262/constant-tables-in-ef-or-npgsql

[smarts]

Would completion of this issue include enabling VALUES expressions in the ANY(…) operator? And if not, is there a separate issue for this functionality that I can upvote?

[roji]

This was actually done for EF 8.0 in dotnet/efcore#30426.

Specifically for Contains, see dotnet/efcore#30732; I'm not sure why you'd want to use VALUES in ANY, when you can simply use x IN (a, b, c) directly (the list inside IN functions just like VALUES). Am I missing something?

[smarts]

@roji if you have a composite key (and therefore index) like ParentId, ChildId i believe using WHERE ("ParentId", "ChildId") in (VALUES(45, 67), VALUES(12, 34)) allows the index to be used.
Edit: I think my syntax is off here. The VALUES keyword isn't necessary in the example above

[roji]

The VALUES keyword isn't necessary in the example above

Exactly, there's no need for it.

Other than that, no, this does not allow for embedding multiple row-values inside IN - nor is that supported anywhere else at the moment. That would mean supporting complex collections, which EF doesn't support yet.