Using GroupBy DateTime with Entity Framework throws an exception

Question

I'm using Entity Framework Core version 3.1.5.

I want to group results by date, let's say by years. My final goal is to have the sum of the value of some property of the entities of a group, but I didn't even manage to group without the sum. I tried all of the answers from the links below, but all of the tries ended up with an exception being thrown, with the message

"System.InvalidOperationException: The LINQ expression 'DbSet.GroupBy(XXX)}, keySelector: YYY)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()".

Code Examples:

var result = await _tenantDbContext.MetricEntities.GroupBy(o => new { o.MetricType, o.CreatedDate.Value.Year }).Select(g => g.Sum(o => o.Value)).ToListAsync();


var result = await _metricRepository.GetQueryable().GroupBy(metric => DbFunctions.TruncateTime(metric.CreatedDate)).OrderBy(group => group.Key).ToListAsync();


var result = await _metricRepository.GetQueryable().GroupBy(metric => DbFunctions.CreateTime(metric.CreatedDate.Value.Year,null,null)).ToListAsync()


var result = _tenantDbContext.MetricEntities
            .GroupBy(x =>
                SqlFunctions.DateAdd("month", SqlFunctions.DateDiff("month", sqlMinDate, x.CreatedDate),
                    sqlMinDate))
            .Select(x => new
            {
                Period = x.Key // DateTime type
            }).ToListAsync();



var result = await _globalDbContext.MetricEntities.GroupBy(x =>
                new
                {
                    Year = x.CreatedDate.Value.Year
                },
            s => new
            {
                InsertCount = s,
            }
        ).Select(g=>new
        {
            InsertCount = g.Count(),
        }).ToListAsync();

Links:

Entity Framework: Efficiently grouping by month

https://atashbahar.com/post/2017-04-27-group-by-day-week-month-quarter-and-year-in-entity-framework

https://entityframework.net/knowledge-base/19225895/linq---grouping-by-date-and-selecting-count

More links:

https://www.codeproject.com/Questions/1199021/LINQ-group-by-month-year-and-return-a-sum

Group by hour in IQueryable

https://www.mikesdotnetting.com/article/257/entity-framework-recipe-grouping-by-year-and-month

EF Core "Group By could not be translated and will be evaluated locally."

https://entityframeworkcore.com/knowledge-base/58102821/translating-query-with-group-by-and-count-to-linq

https://entityframeworkcore.com/knowledge-base/43728317/linq-group-by-method-not-generating-the-expected-sql

https://www.mikesdotnetting.com/article/257/entity-framework-recipe-grouping-by-year-and-month

Hi, can you be more explicit: I see several tables and several result sets. Which ones do you need? To which one does the error applies. If all; please stick with one to start with ;-) — Stefan, Jul 12 '20 at 08:33

score 1 · Answer 1 · answered Jul 12 '20 at 08:11

1

Have a close look at the error, it's about: linq, GroupBy and it selector and it's telling it can not be "translated".

You are trying to do something which is not supported in SQL, most likely calling a C# function in one f the lambda's. So, basically it's saying: either change it or do it in memory.

The quickest fix is to do it in memory, but it can cause a lot of data transfer.

So instead of:

    var restult = db.Table.Where(...).GroupBy(...).ToList();

Try:

    var restult = db.Table.ToList().Where(...).GroupBy(...);
    //or
    var restult = db.Table.Where(...).ToList().GroupBy(...);

answered Jul 12 '20 at 08:11

Stefan

17,448
11
60
79

Thanks. I want to do the group by and the count in the DB and not in the client. – Kfir Ben Haim Jul 12 '20 at 08:26
Yes, so, you want to fix it. Make sure all used functions are translatable to SQL – Stefan Jul 12 '20 at 08:36
How can I check it? I followed other answers from StackOverflow as mentioned in the questions, but none of them worked. Also, I used the SqlFunctions module and DbFunctions, that I guess that are translated to SQL, but yet got an exception – Kfir Ben Haim Jul 12 '20 at 08:45
Your code examples are not really clear. I see some repositories etc. Please try to create a minimal example. Act on the DbContext, without the repository layer in between first. Then show us the actual query and we'll be able to tell what is wrong. – Stefan Jul 12 '20 at 09:41

Kfir Ben Haim · Accepted Answer · 2020-07-15T08:08:02.633

1

The only thing that went for me was:

_dbContext.metricEntities.GroupBy(metricEntity =>new
                    {
                        Year = Microsoft.EntityFrameworkCore.EF.Property<DateTime>(metricEntity, "CreatedDate").Date.Year,
                    }
                ).Select(entities =>
                    new MetricGraphNode
                    {
                        X = new DateTime(entities.Key.Year, 1, 1).ToString("yyyy"),
                        Y = entities.Sum(k => k.Value),
                    }).ToList();

Ofcourse, you can add month, day, hour, etc.

edited Jul 15 '20 at 08:08

answered Jul 12 '20 at 17:37

Kfir Ben Haim

89
5

1

As I said, it's not EF 6.4.4. Next time, when asking EF questions, please use the correct version tag. – Gert Arnold Jul 12 '20 at 20:27

Using GroupBy DateTime with Entity Framework throws an exception

2 Answers2