Support querying over primitive collections

[roji]

There are many query patterns involving arrays parameters which we don't translate (e.g. see #30425).

Doing this would involve a bit of common infrastructure in EF (e.g. support for table valued functions which aren't mapped to entity types). Then, on SQL Server specifically, we could transform the array to a JSON string on the client, and use OPENJSON to expand it out to a rowset; at that point it functions like a normal table, and we should be able to compose LINQ operators on it as usual (similar to #13617 (comment)).

Note that for collections which are inline in the query (e.g. new[] { 1, 2, 3 }), we can translate to SQL VALUES.

PS Pay special attention to the ordering guarantees of the rowset coming out of unnest; we probably need to add an ORDER BY to preserve it.
PPS npgsql/efcore.pg#2677 tracks doing this on PostgreSQL via the unnest function (without needing a JSON string intermediary)

[roji]

Note: this relies on the ability to transform parameter values on the client-side before sending them (e.g. the array parameter is transformed into a JSON representation string of itself). General client-side parameter transformations are tracked by #28028.

The alternative would be to add a full type mapping for array types, value-converting them to their JSON representation. That has a much wider scope of change, and would also allow mapping primitve array properties to JSON columns (#29427)

[roji]

Took a very quick look at this for PostgreSQL, npgsql/efcore.pg#2677 (AKA "it will be a fun little project!) and ran into the following limitations in EF:

• This fails non-subquery translation because the argument to Count is a lambda, and RelationalSqlTranslatingExpressionVisitor.VisitLambda currently throws - we could change it to return NotTranslatedExpression instead.
• However, even if we do that and go into subquery translation, QueryableMethodTranslatingEV only recognizes queryable operators, but in the above query they're enumerable. So that's not the right way forward.
• For normal queries (e.g. ctx.Blogs.Where(b => b.Posts.Count(i => ...))), QueryableMethodNormalizingEV converts the enumerable chain to queryable; but is specifically refrains from doing so for closure variables. We can try removing that and do this even for enumerable closure variables.
• However, NavigationExpandingExpressionVisitor converts the chain back to enumerable. We'd have to make changes to preserve the queryable chain there.

It's probably possible to fix all this, though we should think about the consequences. Doing all this means that all enumerable closure variables will start being treated as queryable.

[ajcvickers]

Note from triage: this is the way.