Entity Framework Core (EF Core) group daily data to monthly
We collect series of meter data from buildings, they are Water, Electricity, Gas, etc. all the data we collect is saved into a DailySnapshot table with the date.
I had to create a chart from Chart JS to display, displaying data with Chart JS is super easy, but getting the data from the server is bit tricky unless you are a SQL expert (well not me :)). Below query will group it by month.
public IQueryable<DailySnapshot> GetAllMonthly(int buildingId, DateTime startTimeStamp, DateTime endTimeStamp)
return Context.DailySnapshots.Where(o =>
o.BuildingId == buildingId
&& o.TimeStamp >= startTimeStamp
&& o.TimeStamp <= endTimeStamp)
.GroupBy(o => new
Month = o.TimeStamp.Month,
Year = o.TimeStamp.Year
.Select(g => new DailySnapshot
TimeStamp = new DateTime(g.Key.Year, g.Key.Month, 1) ,
Electricity = g.Sum(a => a.Electricity),
Gas = g.Sum(a => a.Gas),
Water = g.Sum(a => a.EnergyUse)
Yes I return is as IQueryable, since I use AutoMapper ProjectTo, query get optimised.