Clock grouping with LINQ

Question

I have data that is written every 15 minutes to PowerStringHistorys and PowerCombinerHistorys tables. I am new to LINQ and trying to figure out how to create a query that groups my data by the hour and averages the current one for this hour. That's what i still have

TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time"); DateTime UTC_StartingDate = TimeZoneInfo.ConvertTimeToUtc(StartingDate, easternZone); DateTime UTC_EndingDate = TimeZoneInfo.ConvertTimeToUtc(EndingDate, easternZone); var FirstQ = from p in db.PowerStringHistorys join t in db.PowerStrings on p.string_id equals t.id join u in db.PowerCombiners on t.combiner_id equals u.id join s in db.PowerCombinerHistorys on p.recordTime equals s.recordTime where p.recordTime >= UTC_StartingDate where p.recordTime <= UTC_EndingDate select new { Combiner = u.id, Current = p.current, RecordTime = p.recordTime, Voltage = s.voltage }; 

Now I need to group the combiner and the hour so that I can average the current one and get kwh for each adder for each hour of the specified date range.

I need to somehow apply this simple formula in the query: (average watt per hour) / 1000 = Kwh

So I will end up with something like below. Any help would be greatly appreciated.

 Combiner 1 03/19/2012 1:0:0 1.85 Kwh Combiner 1 03/19/2012 2:0:0 1.98 Kwh Combiner 1 03/19/2012 3:0:0 2.05 Kwh Combiner 1 03/19/2012 4:0:0 2.11 Kwh Combiner 1 03/19/2012 5:0:0 2.01 Kwh Combiner 1 03/19/2012 6:0:0 1.96 Kwh Combiner 1 03/19/2012 7:0:0 1.85 Kwh Combiner 2 03/19/2012 1:0:0 1.77 Kwh Combiner 2 03/19/2012 2:0:0 1.96 Kwh Combiner 2 03/19/2012 3:0:0 2.03 Kwh Combiner 2 03/19/2012 4:0:0 2.11 Kwh Combiner 2 03/19/2012 5:0:0 2.02 Kwh Combiner 2 03/19/2012 6:0:0 1.98 Kwh Combiner 2 03/19/2012 7:0:0 1.83 Kwh Combiner 3 03/19/2012 1:0:0 1.77 Kwh Combiner 3 03/19/2012 2:0:0 1.96 Kwh Combiner 3 03/19/2012 3:0:0 2.03 Kwh Combiner 3 03/19/2012 4:0:0 2.11 Kwh Combiner 3 03/19/2012 5:0:0 2.02 Kwh Combiner 3 03/19/2012 6:0:0 1.98 Kwh Combiner 3 03/19/2012 7:0:0 1.83 Kwh 

EDIT

Above was my original question. After working with the two suggestions I proposed, I received the code that is shown below. Right now, I'm just returning dates and total Kwhs to the view. I plan to drop the stringGroupedKwhlist into HighChart so that the user can view and discard the results of the firstQ query into the Telerik grid so that the user can filter / sort / group so that they can work with the details. Although the code really works and gives the result that I expect, I'm not sure if it is effective. Since I have to loop through foreach usage, I assume that when it gets a lot of data, it may slow down. Is there a more efficient way to handle this?

 using System; using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using AESSmart.Models; namespace AESSmart.Controllers { public class StringKwh { public int CombinerID; public int StringID; public DateTime Interval; public Double KWH; public StringKwh(int combiner, int stringid, DateTime interval, double kwh) { CombinerID = combiner; StringID = stringid; Interval = interval; KWH = kwh; } } public class HomeController : Controller { private readonly AESSmartEntities db = new AESSmartEntities(); public ActionResult Index() { //REPRESENTS DATE RANGE FOR A FULL DAY DateTime startingDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 1); DateTime endingDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59); TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time"); DateTime utcStartingDate = TimeZoneInfo.ConvertTimeToUtc(startingDate, easternZone); DateTime utcEndingDate = TimeZoneInfo.ConvertTimeToUtc(endingDate, easternZone); var firstQ = from p in db.PowerStringHistorys from s in db.PowerCombinerHistorys join t in db.PowerStrings on p.string_id equals t.id join u in db.PowerCombiners on t.combiner_id equals u.id where p.recordTime == s.recordTime where p.recordTime >= utcStartingDate where p.recordTime <= utcEndingDate select new { Combiner = u.id, StringId = p.string_id, Current = p.current, RecordTime = p.recordTime, Voltage = s.voltage }; var groups = firstQ.ToList().GroupBy(q => new { q.Combiner, q.StringId, Date = q.RecordTime.Date, Hour = q.RecordTime.Hour }); List<StringKwh> stringGroupedKwhlist = new List<StringKwh>(); foreach (var group in groups) { stringGroupedKwhlist.Add(new StringKwh( group.Key.Combiner, group.Key.StringId, new DateTime(group.Key.Date.Year, group.Key.Date.Month, group.Key.Date.Day, group.Key.Hour, 0, 0), group.Average(g => g.Voltage * g.Current) / 1000d )); } var groupCombiner = stringGroupedKwhlist.GroupBy(q => new { q.CombinerID }); double myTotalKwh = 0; foreach (var combinerGroup in groupCombiner) { myTotalKwh = Math.Round(combinerGroup.Sum(g => g.KWH), 3); } ViewBag.LifeTimeGeneration = myTotalKwh; ViewBag.myUTCStartDate = utcStartingDate; ViewBag.myUTCEndDate = utcEndingDate; return View(); } public ActionResult About() { return View(); } } } 

Answer 1

This may help you:

 // Group by combiner ID, date, and hour var groups = FirstQ.ToList() .GroupBy(q => new { q.Combiner, Date = q.RecordTime.Date, Hour = q.RecordTime.Hour }); foreach (var group in groups) { var combinerId = group.Key.Combiner; var interval = new DateTime(group.Key.Date.Year, group.Key.Date.Month, group.Key.Date.Day, group.Key.Hour, 0, 0); // power = voltage * current var kwh = group.Average(g => g.Voltage * g.Current) / 1000d; } 

Answer 2

Try replacing select with group by as follows:

  TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time"); DateTime UTC_StartingDate = TimeZoneInfo.ConvertTimeToUtc(StartingDate, easternZone); DateTime UTC_EndingDate = TimeZoneInfo.ConvertTimeToUtc(EndingDate, easternZone); var FirstQ = from p in db.PowerStringHistorys join t in db.PowerStrings on p.string_id equals t.id join u in db.PowerCombiners on t.combiner_id equals u.id join s in db.PowerCombinerHistorys on p.recordTime equals s.recordTime where p.recordTime >= UTC_StartingDate where p.recordTime <= UTC_EndingDate group new { Combiner = u.id, Current = p.current, RecordTime = p.recordTime, Voltage = s.voltage } by new { Combiner = u.id, Date = p.RecordTime.Date, Hour = p.RecordTime.Hour };