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

Answer 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(...);

Answer 2

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.