ENTITY FRAMEWORK GRoupBy,OrderBy,Sum

August 16, 2022

I am trying to rewrite this querry in entity framework (Method syntax) but i can’t,I don’t understand what needs to be done first.
My postgres Querry:

select  id, Sum(yellow_hour)"yellow_hour"
from my_table
group by id
where date_part('day', date::timestamptz - '2022-10-30 00:00:00.000 +0200'::timestamptz)>0
ORDER by yellow_hour desc 
limit 20

My attempt:

var sensors=dbContext.mytable.Where(x=>x.Date >= instantStartDate && x.Date<= instantEndDate).GroupBy(x=>x.id).ToList();

Error
Unable to translate the given ‘GroupBy’ pattern. Call ‘AsEnumerable’ before ‘GroupBy’ to evaluate it client-side.

>Solution :

Although not having access to a compiler right now there might be some typo’s but you will get the idea with the current LINQ query.

var queryDate = DateTime.Parse("2022-10-30 00:00:00.000 +0200");
db.my_table.Where(t=>t.date > queryDate).GroupBy(t => t.id).
              Select(g => new 
                {
                    g.Key, 
                    SUM = g.Sum(s=>s.yellow_hour)
                }).OrderByDescending(g=>g.SUM).Take(20);