SELECT and SUM in Entity Framework

I want to sum all the columns and where context between dates, basically I want to convert the below SQL query to EF:

select meterCategory, sum(cost) maxCost
from [dbo].[UsageData]
where date between '2019-06-25' and '2019-06-25' and 
      cost >= 1
group by meterCategory
order by maxCost desc

Answer

(var startDate, var endDate) = (new DateTime(2019, 6, 25), new DateTime(2019, 6, 25));

var result = 
  await dbContext.UsageDatas
  .Where(ud => ud.Cost >= 1 && ud.Date >= startDate && ud.Date <= endDate)
  .GroupBy(ud => ud.MeterCategory)
  .Select(g => new { MeterCategory = g.Key, MaxCost = g.Sum(c => c.Cost) })
  .OrderByDescending(g => g.MaxCost)
  .ToListAsync();

You can also use tuples and even name their properties, instead of anonymous classes.

Or since you’re SQL oriented, you might want to use C# LINQ query syntax (this one uses tuple):

var query =
  from ud in dbContext.UsageDatas
  where ud.Cost >= 1 && ud.Date >= startDate && ud.Date <= endDate
  group ud by ud.MeterCategory into g
  select (MeterCategory: g.Key, MaxCost: g.Sum(ud => ud.Cost)) into r
  orderby r.MaxCost descending
  select r;

var result = await query.ToListAsync();