Query/Json: projecting individual properties of JSON collection with Distinct fails due to insufficient information
- 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
Assessment
This issue has not been assessed yet.