CODEWITHSUNDEEP
linqentity-frameworksumaggregate
Faisal Mq
Faisal Mq

Reputation: 5534

How to get aggregate data weekwise using Entity Framework

I have a backend table named "VideoData" which has data in the following form:

    VideoID       RecordingStarted         RecordingEnded
    ==============================================================
    abc123        2013-03-01 15:30:00      2013-03-01 15:40:00        
    def123        2013-03-06 12:00:00      2013-03-06 12:40:00
    ijk123        2013-03-10 11:00:00      2013-03-10 11:05:00
    klm123        2013-03-12 10:05:00      2013-03-12 10:25:00
    And list goes on .......
    .......................
    .............................

Using Entity Framework I want to get Total Hours of Video captured for example for the month of March 2013, in such a way that total hours of captured video come as Weekwise. Example in the following manner:

    Mar 1, 2013     Mar 8, 2013      Mar 15, 2013     Mar 22, 2013
   ================================================================
   500              300              350              200

I have googled much, but could not figure out a way on how to do it exactly. Please guide. Thanks for your help.

Upvotes: 1

Views: 2079

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109117

I hate to overrule Ilya's excellent job (sorry mate, at least I upvoted you), but since this is entity framework it is possible to let the database do the job in just one query by using SqlFunctions:

context.Videos.Select(t => new
  {
      Year = SqlFunctions.DatePart("yyyy", t.RecordingStarted),
      Week = SqlFunctions.DatePart("ww", t.RecordingStarted),
      Hours = SqlFunctions.DateDiff("hh", t.RecordingStarted, t.RecordingEnded)
  })
.GroupBy(x => new { x.Year, x.Week} )
.Select (x => new { x.Key.Year, x.Key.Week, TotalHours = x.Sum(p => p.Hours)} )

The output will be something like

2013  9 500
2013 10 300
...

To get from year + week to a date is remarkably hard in Sql Server. If this really is a requirement you may consider building a reference table with year + week numbers and date of the first day. Or fetch the data in memory (.ToList()) and use C# to convert the data.

Upvotes: 6

Ilya Ivanov
Ilya Ivanov

Reputation: 23626

So ok, this task seems to be quite interesting. I've implemented basic functionality with local data. Fell free to comment for more functionality. It's a little-bit messy thou

//local data for testing
var data = new[] {
    new {VideoId = "abc123",RecordindStarted = DateTime.Parse("2013-03-01 15:30:00"),RecordingEnded = DateTime.Parse("2013-03-01 15:40:00")},        
    new {VideoId = "def123",RecordindStarted = DateTime.Parse("2013-03-06 12:00:00"),RecordingEnded = DateTime.Parse("2013-03-06 12:40:00")},
    new {VideoId = "de1223",RecordindStarted = DateTime.Parse("2013-03-06 12:30:00"),RecordingEnded = DateTime.Parse("2013-03-06 12:50:00")},
    new {VideoId = "ijk123",RecordindStarted = DateTime.Parse("2013-03-10 11:00:00"),RecordingEnded = DateTime.Parse("2013-03-10 11:05:00")},
    new {VideoId = "klm123",RecordindStarted = DateTime.Parse("2013-03-12 10:05:00"),RecordingEnded = DateTime.Parse("2013-03-12 10:25:00")},
    new {VideoId = "klm123",RecordindStarted = DateTime.Parse("2013-03-12 10:05:00"),RecordingEnded = DateTime.Parse("2013-03-13 10:25:00")},
};


var calendar = new GregorianCalendar();

var ungroupedTotalHours = data.Select(d => GetHoursPerDays(d.RecordindStarted, d.RecordingEnded));

var groupedTotalHours = 
         ungroupedTotalHours.SelectMany(v => v)
                            .GroupBy(v=> v.Key)
                            .ToDictionary(v => v.Key, v => v.Sum(val => val.Value));

var result = 
 groupedTotalHours.GroupBy(v =>calendar.GetWeekOfYear( v.Key, CalendarWeekRule.FirstDay, DayOfWeek.Monday))
                  .ToDictionary(v => "Week "+ v.Key, row => row.Sum(val => val.Value));

Console.WriteLine ( string.Join(Environment.NewLine, result.Select(r => r.Key +" has "+r.Value+" hours")) );

core logic goes to this method:

public IDictionary<DateTime, int> GetHoursPerDays(DateTime start, DateTime end)
{
    if(end.Date == start.Date)
        return new Dictionary<DateTime, int>{{start.Date, (end -  start).Minutes}};
    return Enumerable.Range(1, (int)(end -  start).TotalHours)
                    .Select(v => start.AddHours(v))
                    .GroupBy(v => v.Date)
                    .ToDictionary( v => v.Key, r => r.Count());
}

prints:

Week 9 has 10 hours
Week 10 has 65 hours
Week 11 has 44 hours

Upvotes: 2

Related Questions