Group By Is Being Translated Locally? How to Fix this?

Question

I am seeing this "warning" in my logs. I am using EF Core 2.2.4

The LINQ expression 'GroupBy(new <>f__AnonymousType95`2(Date = [x].CreatedDate.Date, EmployeeId = [x].EmployeeId), [x])' could not be translated and will be evaluated locally

my query looks like this

var groupedEvents = dbContext.EventTrackings
    .Where(x => // where clause )
    .GroupBy(x => new { x.CreatedDate.Date, x.EmployeeId })
    .OrderBy(x => x.Key.Date)
    .ToList();

If you remove the `Date` do you still get the warning? I mean use just `x.CreatedDate` — panoskarajohn, Jan 08 '20 at 18:51
Probably because of `x.CreatedDate.Date`, which generates a short date out of your date. Try without that function or add another (computed) column to the table that hold the ShortDate value. — Charles, Jan 08 '20 at 18:52
I thought that may be the reason, so I removed CreatedDate completely but still see it.The LINQ expression 'GroupBy(new <>f__AnonymousType95`1(EmployeeId = [x].EmployeeId), [x])' could not be translated and will be evaluated locally — chobo2, Jan 08 '20 at 18:59
@chobo2 avoid the use of `new` inside group by https://stackoverflow.com/questions/58102821/translating-query-with-group-by-and-count-to-linq now your warning should not be there — panoskarajohn, Jan 08 '20 at 19:04
how would I do the multiple grouping then? Also I did try remvoing the new and still seeing it. The LINQ expression 'GroupBy([x].EmployeeId, [x])' could not be translated and will be evaluated locally. — chobo2, Jan 08 '20 at 19:06
@chobo2 you are coreect you need this. I will do some testing. In the meantime does this help ? https://stackoverflow.com/questions/57807038/ef-core-group-by-could-not-be-translated-and-will-be-evaluated-locally — panoskarajohn, Jan 08 '20 at 19:11
It is weird that I am getting it with anything in that groupBy event he most simple one. I would try 3.0 but I would have to go to core 3.0 I guess and don't really want to take that big of a task on. — chobo2, Jan 08 '20 at 19:15
@chobo2 All you'll get if you go to EF Core 3.0+ is runtime exception instead of warning. The problem is not the key, but the whole LINQ `GroupBy` w/o select of key/aggregates, which has no SQL equivalent. For more info, see EF Core docs - [Complex Query Operations - Group By](https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupby). — Ivan Stoev, Jan 09 '20 at 07:07

vendettamit · Answer 1 · 2020-01-08T19:28:37.587

0

Try creating named property in your anonymous type .GroupBy(x => new { CreatedDate = x.CreatedDate.Date, x.EmployeeId }). I think it's not able to find and convert the Date property name to DB table column name if your actual db column name is CreatedDate.

var groupedEvents = dbContext.EventTrackings
    .Where(x => // where clause )
    .GroupBy(x => new { CreatedDate = x.CreatedDate.Date, x.EmployeeId })
    .OrderBy(x => x.Key.CreatedDate)
    .ToList();

edited Jan 08 '20 at 19:28

answered Jan 08 '20 at 19:25

vendettamit

14,315
2
32
54

So you suggesting to make a concrete type? The weird thing is I tried something simple like " .GroupBy(x => x.EmployeeId )" but I getting the same "warning" – chobo2 Jan 08 '20 at 19:26
`x.Created.Date` will generate property name `Date` on anonymous type so it's just a named property it's still antonymous type. Added a sample for clarification. – vendettamit Jan 08 '20 at 19:28
Still getting the same thing: The LINQ expression 'GroupBy(new <>f__AnonymousType95`2(CreatedDate = [x].CreatedDate.Date, EmployeeId = [x].EmployeeId), [x])' could not be translated and will be evaluated locally. – chobo2 Jan 08 '20 at 19:32
Can you try removing your `OrderBy()`? – vendettamit Jan 08 '20 at 19:51
Yea I removed it awhile back and same thing. – chobo2 Jan 08 '20 at 19:52
hmmm.. I can't find any [tests on EFCore 2.2](https://github.com/dotnet/efcore/blob/release/2.2/src/EFCore.Specification.Tests/Query/GroupByQueryTestBase.cs) supporting the grouping by anonymous type for multiple fields. May be it's not supported in this version. – vendettamit Jan 08 '20 at 19:57
Only thing I found was: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1 – chobo2 Jan 08 '20 at 20:04
The one thing, I see is that they use "Select" which seems to be the key to get rid of the error, I am playing around it right now. Kinda sucks though as that means you going to have to write all properties you want in the select. – chobo2 Jan 08 '20 at 20:06
Sadly even in EFCore 3.0 instead of fixing the actual issue.. I found this [issue/request](https://github.com/dotnet/efcore/issues/14935) from MS team suggesting disabling such warnings. – vendettamit Jan 08 '20 at 20:49
hmm so it is planned but nothing in the work. I wonder what the performance hit is right now. with using GroupBy as I been using it vs going through the effort of doing the select to make the error go away. – chobo2 Jan 08 '20 at 20:54

Group By Is Being Translated Locally? How to Fix this?

1 Answers1