C# get minute number from date in linq group by

Question

I need to group my data by each minute of the day and get the count of the events that occurred during that minute. I currently have:

                items.GroupBy(x => new
                {
                    x.date.Minute,
                    x.date.Hour
                })
                .Select(x => new TransferObject
                {
                    Minute = x.Key.Minute,
                    Hour = x.Key.Hour,
                    Count = x.Count(),
                    Day = date
                }).OrderBy(x => x.Hour).ThenBy(x => x.Minute).ToList();

This does what I need, but the problem is that I may not have data points for each minute, how could I add 0 to Count field if I don't have data for that minute? Alternatively I could add minute number (0...1440) and add the missing values later.

EDIT

The solution currently groups by the starting date only, but that object actually has a field end_date. So basically at the moment I have all the events that started on that minute, but I need to get the count of events that were running at that minute.

The data I have contains:

date                    end_date
2015-05-15 09:52:15.650 2015-05-15 09:55:38.097
2015-05-15 09:52:15.633 2015-05-15 09:52:16.097
2015-05-15 09:52:11.633 2015-05-15 09:52:13.047
2015-05-15 09:51:49.097 2015-05-15 09:55:17.687
2015-05-15 09:51:49.087 2015-05-15 09:56:17.510

At the moment the it does not use the end_date field so the output is

{Count:2;Hour:9;Minute:51}  
{Count:3;Hour:9;Minute:52}  

I need to have all the events that were running, something like

{Count:2;Hour:9;Minute:51}  
{Count:5;Hour:9;Minute:52}
{Count:3;Hour:9;Minute:53}
{Count:3;Hour:9;Minute:54}
{Count:3;Hour:9;Minute:55}
{Count:2;Hour:9;Minute:56}

Answer 1

It seems, that you want GroupJoin() not just GroupBy(), something like that:

  var source = ... // Your query;

  var result = Enumerable
    .Range(0, 1440) // All the minutes that should be in the result
    .GroupJoin(source, 
       minutes => minutes, 
       item => item.Hour * 60 + item.Minute,
       (minutes, items) => new {
          hour = minutes / 60,
          minute = minutes % 60,
          count = items.Count() } ); // Count() can well return 0...

Answer 2

Firstly you could generate all hours & minutes in a day using some simple LINQ:

var all = Enumerable.Range(0,24)
                    .SelectMany(x => Enumerable.Range(0,60),
                                (x,y) => new {Hr = x, Min=y});
foreach(var a in all)
    Console.WriteLine("{0}:{1}",a.Hr,a.Min); // 00:00 thru 23:59

Live example: http://rextester.com/NPNKN82691

You could then use this collection to join to your original results to form the final output:

var all = Enumerable.Range(0,24)
                    .SelectMany(x => Enumerable.Range(0,60),
                               (x,y) => new {Hr = x, Min=y});

var items = new[]{
        new Item{Date = new DateTime(2015,01,01,0,1,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0)},
        new Item{Date = new DateTime(2015,01,01,0,3,0)}
};

var results = all.GroupJoin(items,
                            x => new {Hour = x.Hr,Minute = x.Min},
                            y => new {Hour = y.Date.Hour,Minute = y.Date.Minute},
                            (x,y) => new {Hour = x.Hr, Minute = x.Min, Count = y.Count()});

foreach(var result in results)
   Console.WriteLine("{0:00}:{1:00} = {2}",result.Hour, result.Minute, result.Count);

Output

00:00 = 0
00:01 = 6
00:02 = 0
00:03 = 1
00:04 = 0
//..snip..//
23:55 = 0
23:56 = 0
23:57 = 0
23:58 = 0
23:59 = 0

Live example http://rextester.com/OAYZ95244

---

EDIT

Given your change of requirement to consider a range of dates, you could add a new class and method used to extrapolate the hour & minute from each item:

 // classes used
 public class ExtrapolatedItem
 {
     public int Hour{get;set;}
     public int Minute{get;set;}
 }

public class Item{
    public DateTime Date{get;set;}
    public DateTime EndDate{get;set;}
}

// method 

private static IEnumerable<ExtrapolatedItem> Extrapolate(Item item)
{
    for(var d = item.Date;d<=item.EndDate; d = d.AddMinutes(1))
    {
        yield return new ExtrapolatedItem{ Hour = d.Hour, Minute = d.Minute};
    }
}

This can then be used in the original method like this:

var items = new[]{
        new Item{Date = new DateTime(2015,01,01,0,1,0),EndDate = new DateTime(2015,1,1,0,5,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0),EndDate = new DateTime(2015,1,1,0,5,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0),EndDate = new DateTime(2015,1,1,0,5,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0),EndDate = new DateTime(2015,1,1,0,5,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0),EndDate = new DateTime(2015,1,1,0,5,0)},
        new Item{Date = new DateTime(2015,01,01,0,1,0),EndDate = new DateTime(2015,1,1,0,5,0)},
        new Item{Date = new DateTime(2015,01,01,0,3,0),EndDate = new DateTime(2015,1,1,0,5,0)}
};
var results = all.GroupJoin(items.SelectMany(Extrapolate), // extrapolate each item to a list of extrapolated items
                            x => new {Hour = x.Hr,Minute = x.Min},
                            y => new {Hour = y.Hour,Minute = y.Minute},
                            (x,y) => new {Hour = x.Hr, Minute = x.Min, Count = y.Count()});

output

00:00 = 0
00:01 = 6
00:02 = 6
00:03 = 7
00:04 = 7
00:05 = 7
00:06 = 0
00:07 = 0
//..snip..//

Live example: http://rextester.com/VOVJCS56741