Grouping by hour using LINQ

Question

I have data being recorded every 15 minutes into the PowerStringHistorys and PowerCombinerHistorys tables. I am new to LINQ and am trying to figure out how to create a query that groups my data by each hour and for that hour average the current. Here is what I have so far

        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 by combiner and hour so I can average the current and get the kwh for each combiner for every hour of the date range specified.

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

So what I will end with is something like the 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 received I ended up with the code that is displayed below. Right now I am just returning the dates and total Kwhs to the view. I do plan on throwing the stringGroupedKwhlist list into a HighChart for the user to view and throwing the firstQ query results into a Telerik grid for the user to filter/sort/group on so they can work with the details. While the code does work and produces the result I am expecting, I am not sure it is efficient. Since I have to cycle through using a foreach I am guessing once it gets a lot of data it could slow down. Is there a more efficient way of handling 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 might get you started:

// 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;
}