CODEWITHSUNDEEP
c#linqdatetimedatatablegroup-by
Faraday
Faraday

Reputation: 2954

Group DateTime rows in datatable by DateTime - C#

I have a LARGE datatable (500k-1m rows), without going into detail this is a requirement as the end user needs/wants to be able to see all of the data. This is on a local server so bandwidth etc are not concerns for me.

I have a DateTime field in the DataTable which I need to group, let me explain what I mean by grouping... It's probably not what you think I mean (from looking at the other questions on here!).

        var table = new DataTable();
        table.Columns.Add("EventTime", typeof(DateTime));
        table.Columns.Add("Result", typeof(String));
        table.Columns.Add("ValueOne", typeof(Int32));
        table.Columns.Add("ValueTwo", typeof(Int32));
        table.Rows.Add("2012-02-06 12:41:45.190", "A", "7", "0");
        table.Rows.Add("2012-02-06 12:45:41.190", "B", "3", "89");
        table.Rows.Add("2012-02-06 12:59:41.190", "C", "1", "0");
        table.Rows.Add("2012-02-06 13:41:41.190", "D", "0", "28");
        table.Rows.Add("2012-02-06 17:41:41.190", "E", "0", "37");
        table.Rows.Add("2012-02-07 12:41:45.190", "F", "48", "23");

I would expect the above table to be grouped so that I get a sum of the "ValueOne" column, and an average of the "ValueTwo" column. I need the grouping to be a little bit flexible so that I can specify that I want grouping by minutes (only the first and last rows would be grouped, the rest would just provide their values), or by days (all but the last row would be grouped into a single row), etc.

I've tried this a few times but I'm getting no where. My LINQ knowledge isn't great, but I thought I'd be able to do this!

Note: The DataTable is already on the machine for calculations/views which cannot be changed, so saying "Stop being an idiot, filter in SQL!!!" is a valid answer, just useless to me! :-D

Also, in case you missed it in the title, I need this in C# - I'm working with .NET 4.0...

Thanks in advance, assuming you decide to help! :-)

Upvotes: 4

Views: 6386

Answers (4)

David Nelson
David Nelson

Reputation: 3714

The other three answers are close, but as you pointed out they group events that occurred in the same second of the minute, not events that happened in the same second, which is what you want. Try this:

var query = from r in table.Rows.Cast<DataRow>()
        let eventTime = (DateTime)r[0]
        group r by new DateTime(eventTime.Year, eventTime.Month, eventTime.Day, eventTime.Hour, eventTime.Minute, eventTime.Second)
            into g
        select new {
                g.Key,
                Sum = g.Sum(r => (int)r[2]),
                Average = g.Average(r => (int)r[3])
            };

You can adjust what information you pass to the DateTime constructor to group by different time parts.

Upvotes: 5

James Johnson
James Johnson

Reputation: 46047

Something like this should work:

DataTable dt = GetDataTableResults();

var results = from row in dt.AsEnumerable()
              group row by new { EventDate = row.Field<DateTime>("EventTime").Date } into rowgroup
              select new
              {
                  EventDate = rowgroup.Key.EventDate,
                  ValueOne = rowgroup.Sum(r => r.Field<int>("ValueOne")),
                  ValueTwo = rowgroup.Average(r => r.Field<decimal>("ValueTwo"))
              };

Upvotes: 1

StriplingWarrior
StriplingWarrior

Reputation: 156459

Here's what your baseline code could look like:

var query = table.Rows.Cast<DataRow>()
    .GroupBy(r => ((DateTime)r[0]).Second)
    .Select(g => new
                 {
                    g.Key, 
                    Sum = g.Sum(r => (int)r[2]),
                    Average = g.Average(r => (int)r[3])
                 });

To add flexibility, you could have something like this:

IEnumerable<IGrouping<object, DataRow>> Group(IEnumerable<DataRow> rows, GroupType groupType)
{
    // switch case would be preferable, but you get the idea.
    if(groupType == GroupType.Minutes) return rows.GroupBy(r => ((object)((DateTime)r[0]).Minute));
    if(groupType == GroupType.Seconds) return rows.GroupBy(r => ((object)((DateTime)r[0]).Second));
    ...
}

var baseQuery = table.Rows.Cast<DataRow>();
var grouped = Group(baseQuery, groupType);
var query = grouped
    .Select(g => new
                 {
                    g.Key, 
                    Sum = g.Sum(r => (int)r[2]),
                    Average = g.Average(r => (int)r[3])
                 });

Upvotes: 0

Aducci
Aducci

Reputation: 26634

The only thing you need to change is the property you want to group by. 

var query = from x in DataSource
            group x by x.EventTime.Minute into x
            select new
            {
              Unit = x.Key,
              SumValueOne = x.Sum(y => y.ValueOne),
              AverageValueTwo = x.Average(y => y.ValueTwo), 
            };

Upvotes: 1

Related Questions