Group by Time(Only hrs part) in linq

Question

I have a datatable that has following columns

CallTime   CallType

15:45:00    Voice
15:30:54    Voice
00:12:14    SMS
01:13:47    Voice
05:48:23    SMS
12:00:47    Voice

Now what i want Using Linq Result Like This

Hrs   Count
00    1
01    1
05    1
12    1
15    2

I have tried Group by using Linq, But not getting it right.

I used the following code

   var groupQuery = from table in Foundrows.AsEnumerable()
                         group table by table["Call Time"] into groupedTable
                         select new
                         {
                             CallTime = groupedTable.Key,
                             CallCount = groupedTable.Count()
                         };

Answer 1

var groupQuery = from r in Foundrows.AsEnumerable()
                 group r by r.Field<TimeSpan>("CallTime").Hours into g
                 select new {
                    Hrs = g.Key,
                    CallCount = g.Count()
                 };

If your column of string type then just parse it's value into TimeSpan:

var groupQuery = from r in Foundrows.AsEnumerable()
                 let time = TimeSpan.Parse(r.Field<string>("CallTime"))
                 group r by time.Hours into g
                 select new {
                    Hrs = g.Key,
                    CallCount = g.Count()
                 };

---

Foundrows.AsEnumerable()
         .Select(r => {
             TimeSpan time;
             return new {
                Row = r,
                Time = TimeSpan.TryParse(r.Field<string>("CallTime"), out time) ?
                       time : (TimeSpan?)null
             };
         })
         .Where(x => x.Time.HasValue)
         .GroupBy(x => x.Time.Value.Hours)
         .Select(g => new {
             Hrs = g.Key,
             CallCount = g.Count()
         });

Or you can use TryParse which will return nullable TimeSpan method like this:

public static TimeSpan? TryParse(string s)
{
    TimeSpan time;
    if (TimeSpan.TryParse(s, out time))
       return time;

    return null;
}

And use it in original query:

var groupQuery = from r in Foundrows.AsEnumerable()
                 let time = TryParse(r.Field<string>("CallTime"))
                 where time.HasValue
                 group r by time.Value.Hours into g
                 select new {
                    Hrs = g.Key,
                    CallCount = g.Count()
                 };

Answer 2

If you want to group by hour and the field "Call Time" is a DateTime field:

var groupQuery = from row in Foundrows.AsEnumerable()
                 let hour = row.Field<DateTime>("Call Time").Hour 
                 group row by hour into hourGroup
                 select new {
                    CallHour = hourGroup.Key,
                    CallCount = hourGroup.Count()
                 }; 

If you want to group by the combination of the Date + Hour:

var groupQuery = from row in Foundrows.AsEnumerable()
                 let date = row.Field<DateTime>("Call Time")
                 let hour = date.Hour
                 group row by new{ date, hour } into dateHourGroup
                 select new {
                     CallDate = dateHourGroup.Key.date,
                     CallHour = dateHourGroup.Key.hour,
                     CallCount = dateHourGroup.Count()
                 }; 

Edit: If it's actually a TimeSpan (as it seems to be), use lazyberezovsky's approach :)

Your comment: If i wanna use TryParse instead of Parse, What changes would i need to make in code?

I would recommend to create an extension which allows to return a Nullable<TimeSpan>, then you can check if HasValue==true:

public static TimeSpan? TryGetTimeSpan(this string tsString, string format = null)
{
    TimeSpan ts;
    bool success;
    if (format == null)
        success = TimeSpan.TryParse(tsString, out ts);
    else
        success = TimeSpan.TryParseExact(tsString, format, null, TimeSpanStyles.None, out ts);
    return success ? (TimeSpan?)ts : (TimeSpan?)null;
} 

Now this query works efficiently and don't uses undocumented side-efects (as a local TimeSpan variable that you use for TimeSpan.TryParse in the query):

var groupQuery = from r in Foundrows.AsEnumerable()
                 let time = r.Field<string>("CallTime").TryGetTimeSpan()
                 where time.HasValue
                 group r by time.Value.Hours into g
                 select new
                 {
                     Hrs = g.Key,
                     CallCount = g.Count()
                 };