dotnet / dotnet/efcore

Query/Json: projecting individual properties of JSON collection with Distinct fails due to insufficient information

Open
#31,397 0 comments 0 reactions 0 assignees View on GitHub
area-json area-query needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

query:

```cs
ss.Set()
.OrderBy(x => x.Id)
.Select(x => x.OwnedCollectionRoot.Select(xx => xx.Name).Distinct().ToList())
.AsNoTracking()
```

problem:

each collection needs some sort of identifier so that we can bucket results correctly. For normal entities we use keys, for JSON we use .key property coming from OPENJSON. However, if you project individual properties, we can't use that, instead we try to use all of the projected elements (since they are now guaranteed to be unique, due to DISTINCT operation).

We do that in SelectExpression.ApplyDistinct. However, for JSON collection projecting a property, we generate projection looking like this:

```sql
CASE
WHEN o.Number != NULL THEN o.Name
END
```

which we can't use as identifier because we only support column expressions. We either need to support arbitrary sql expressions as identifiers or clean up the projection here. @roji

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.