CODEWITHSUNDEEP
c#linqdatatable
Harshit
Harshit

Reputation: 5157

LINQ get the data from the datatable group by hours

I have a Datatable which contains the data like this : 

name      Code    dateAndTime
ab01     3hdfj3   2012-06-24 14:17:32.833
ab02     57dfdf   2012-07-12 14:08:28.210
ab02     57dfdf   2012-07-12 14:08:28.210
ab02     57dfdf   2012-07-12 15:08:28.210
ab03     57dfdf   2012-07-12 14:08:28.210
ab03     9jdhfj   2012-07-12 15:15:12.007
etc....

Now i want to filter the data from the datatable via LINQ. I want to get the count of records group by hour. My day starts at 7 O Clock and ends at next day's 7 O Clock. So, i want to get the data of 24 hours group by hour. This is the first condition.

Second condition is, I want to the data also group by code. So the condition becomes like this :

Table :

Name     7 OClock  8 O Clock   9 O Clock  etc.....
ab01     56           75          88
ab02     45           45          77
ab03     57           66          67
etc...

In the above table generated, the columns should increment group by hours & rows should increment group by Name. I got the current code to get the count of columns group by hours. Here "dt" for DataTable

var query = dt.AsEnumerable()
    .GroupBy(row => row.Field<DateTime>("dateAndTime").Hour)
    .Select(grp => new
    {
        Hour = grp.Key,
        Count = grp.Count()
    });

                foreach (var x in query)
                {

                }
            }

How can i achieve the above condition ?

Upvotes: 1

Views: 1202

Answers (1)

vortex
vortex

Reputation: 1058

You can group by two conditions like this:

var query = dt.AsEnumerable()
    .GroupBy(row => new
               {
                   Hour = row.Field<DateTime>("dateAndTime").Hour,
                   Code = row.Field<returnTypeOFCodeColumn>("Code")
               })
    .Select(g => new 
               {
                   Hour = g.Key.Hour,
                   Code = g.Key.Code, 
                   Count = g.Count()
               }
        );

foreach (var item in query)
{
    //access properties here 
    //item.Hour, item.Code, item.Count  
}

Upvotes: 2

Related Questions