dotnet / dotnet/efcore

Translate inline collections to JSON arrays where relevant

Open
#31,501 0 comments 0 reactions 0 assignees View on GitHub
area-primitive-collections area-query
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.