Translate inline collections to JSON arrays where relevant
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
Since 8.0, we support inline collections (`new[] { ... }`) inside the query in various contexts; we mostly allow LINQ operators to be composed over them, translating the NewArrayExpression to a SQL VALUES expression e.g. (`SELECT ... FROM VALUES(...)`).
However, there are scenarios where an operation needs to happen on the array as a whole, as opposed to an element; the obvious example is comparing arrays (see PrimitiveCollectionsQueryTestBase for the following test):
```c#
[ConditionalTheory]
[MemberData(nameof(IsAsyncData))]
public virtual Task Column_collection_equality_parameter_collection(bool async)
{
var ints = new[] { 1, 10 };
return AssertQuery(
async,
ss => ss.Set().Where(c => c.Ints == ints),
ss => ss.Set().Where(c => c.Ints.SequenceEqual(ints)),
entryCount: 1);
}
```
It's not possible to compare relational sets in SQL (they're also un-ordered). However, we can identify this and construct a JSON array (string), using [`JSON_ARRAY`](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-array-transact-sql?view=sql-server-ver16) on SQL Server: `WHERE [x].[Ints] = JSON_ARRAY(@__i_0,@__j_0)` (SQLite similarly has [`json_array`](https://www.sqlite.org/json1.html#jarray)). Note that the string representation must turn out to be **exactly** identical for this to work (so no tweaking of the column JSON representation can be allowed).
PostgreSQL already translates the above via a non-JSON array:
```sql
@__i_0='1'
@__j_1='10'
SELECT p."Id", p."Bool", p."Bools", p."DateTime", p."DateTimes", p."Enum", p."Enums", p."Int", p."Ints", p."NullableInt", p."NullableInts", p."String", p."Strings"
FROM "PrimitiveCollectionsEntity" AS p
WHERE p."Ints" = ARRAY[@__i_0,@__j_1]::integer[]
```
Contributor guide
Assessment
This issue has not been assessed yet.