search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.79k stars 3.19k forks source link

Relational: support queries converting a relational rowset to a primitive collection #33792

Open roji opened 5 months ago

roji commented 5 months ago

Certain query forms embed an enumerable (non-scalar) subquery; for example: PrimitiveCollectionsQuerySqlServerTest.Column_collection_Where_equality_inline_collection: ss.Set<PrimitiveCollectionsEntity>().Where(c => c.Ints.Where(i => i != 11) == new[] { 1, 111 }.

To translate this, we must be able to convert the subquery (SELECT i FROM c.Ints WHERE i <> 11) and convert it to a primitive collection. For most databases, this means converting the relational rowset to a JSON array string; in PostgreSQL, a rowset can be converted to an array via array_agg.

Another usage example is the assignment of a primitive collection to a column in ExecuteUpdate (#32494).

Note: this is implemented in Cosmos via the ARRAY() operator.

roji commented 5 months ago

Note: in SQL Azure there's now also JSON_ARRAY_AGG (preview), this is probably a more suitable translation than FOR JSON.