dotnet / dotnet/efcore

subquery not working when i group by date of datetime

Open
#28,977 0 comments 0 reactions 0 assignees View on GitHub
area-groupby area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

i want group by datetime of a date
for example:
2022-03-03 => 2022-03-03 4:20 - 2022-03-03 17:30
2022-03-04 => 2022-03-04 4:20 - 2022-03-04 17:30.
...
when i group by datetime type is ok but when is use datetime.date query not working becuase of inner .ToList() as subquery

```C#
return await query
.Select(x=> new
{
x.Person.FirstName,
x.Person.LastName,
x.VerifyDate,
x.VerifyDate.Date,
x.VerifyState,
x.Id,
x.PersonId,
x.DeviceType,
x.Person.UserCode
})
.GroupBy(x=>new {Date=x.Date,x.PersonId})
.Select(x=>new TrafficListDto()
{
DateTime = x.Key.Date,
FullName =x.First().FirstName+" "+x.First().LastName,
UserCode = x.First().UserCode,
DeviceType = x.First().DeviceType,
TrafficsDtos = x.Select(v=> new TrafficsDto()
{
Id=v.Id,
TrafficTime = v.VerifyDate,
Status = v.VerifyState
})
.ToList()


})
.OrderBy(x=>x.DateTime)

.Skip(skip)
.Take(take)
.ToListAsync();
```

that gives me such a message

```
Unhandled exception rendering component: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly gen
erate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These sho
uld either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.
System.InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate r
esults on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should eit
her contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.

```

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.