C#•3y ago

GroupBy cannot be translated using

Hello I can't for example group data by month using one of class datetime attribute navigation property month

 var countryHolidays = await _db.Holidays
                .Where(x => x.Country.CountryCode == countryCode && x.HolidayDate.Year == year)
                .Include(x => x.Country)
               .GroupBy(x => new { 
                    Month = x.HolidayDate.Month
            }).ToListAsync();

 var countryHolidays = await _db.Holidays
                .Where(x => x.Country.CountryCode == countryCode && x.HolidayDate.Year == year)
                .Include(x => x.Country)
               .GroupBy(x => new { 
                    Month = x.HolidayDate.Month
            }).ToListAsync();

12 Replies

Zendist•3y ago

Do you get an error?

kaziuxOP•3y ago

Yes. InvalidOperationException that query can't be translated https://www.inoaspect.com.au/entity-framework-core-ef-core-group-daily-data-to-monthly/ I was guided by this example

System.InvalidOperationException: The LINQ expression 'DbSet<Holiday>()
    .Where(x => x.Country.CountryCode == __countryCode_0 && x.HolidayDate.Year == __year_1)
    .Include(x => x.Country)
    .GroupBy(x => new { 
        Month = x.HolidayDate.Month, 
        Year = x.HolidayDate.Year
     })' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

System.InvalidOperationException: The LINQ expression 'DbSet<Holiday>()
    .Where(x => x.Country.CountryCode == __countryCode_0 && x.HolidayDate.Year == __year_1)
    .Include(x => x.Country)
    .GroupBy(x => new { 
        Month = x.HolidayDate.Month, 
        Year = x.HolidayDate.Year
     })' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Jayy•3y ago

that tutorial wont even load

 var countryHolidays = await _db.Holidays
                .Where(x => x.Country.CountryCode == countryCode && x.HolidayDate.Year == year)
                .Include(x => x.Country)
               .GroupBy(x => x.HolidayDate.Month)
                .ToListAsync();

 var countryHolidays = await _db.Holidays
                .Where(x => x.Country.CountryCode == countryCode && x.HolidayDate.Year == year)
                .Include(x => x.Country)
               .GroupBy(x => x.HolidayDate.Month)
                .ToListAsync();

try this @kaziux

kaziuxOP•3y ago

I did exactly the same way, but I get still the same exception

Jayy•3y ago

try with Month.Name Or whatever Groupby is very finicky and i can't see ur entities

Saber•3y ago

you have to select something after grouping

kaziuxOP•3y ago

so may query order is bad? Impossible to use, because month itself is int property

Jayy•3y ago

ahh, in that case ya try the select way that entity said i missed you didnt have one

Saber•3y ago

and if you are grouping purely to just group, not doing any aggregations you may just need to do that after the query is materialized

kaziuxOP•3y ago

I'm only do filtering query. No any agregrations like counting, average or something.

Saber•3y ago

grouping would have to be done on the c# side then after the query is completed as that doesn't make sense in sql

kaziuxOP•3y ago

I barely use grouping queries. I forgot that query is for aggregations

Gaming

Programming

GroupBy cannot be translated using