locked
LINQ Group and Sum table

    Question

  • I'm having the hardest time wrapping my mind around LINQ.

     

    I have a table containing employee schedules and hours worked.  I want to generate a table that groups the data by employee id and weekending date, and sum's their hours worked.

     

    schedules:

    empid    hours    date    weekending

    5311      4         1/3/08   1/5/2008

    5311      5         1/3/08   1/5/2008

    9983      1         1/3/08   1/5/2008

    9983      2         1/3/08   1/5/2008

    5311      3         1/7/08   1/12/2008

    5311      7         1/8/08   1/12/2008

     

    I want to generate this:

     

    5311       1/5/2008      9

    9983       1/5/2008      3

    5311       1/12/2008    10

     

     

    Thursday, January 10, 2008 1:12 AM

Answers

  • Here's what your query would look like.

     

    from s in db.Schedules

    group s by new {s.empid, s.weekending} into g

    select new { g.Key.empid, g.Key.weekending, g.Sum(s => s.hours) };

     

    The interesting part here is that you are grouping by two key conditions.  Yet a LINQ group by operator can only group by one condition.  So what you have to do is combine the conditions into a single anonymous type.  Once you've grouped, you now have a new variable 'g' instead of 's'.  Each instance of 'g' contains one of the group key values and a collection of the items in the group.  You can then use the Sum() operator on the collection to compute the sum over the hours. 

     

     

    Thursday, January 10, 2008 3:19 AM

All replies

  • Here's what your query would look like.

     

    from s in db.Schedules

    group s by new {s.empid, s.weekending} into g

    select new { g.Key.empid, g.Key.weekending, g.Sum(s => s.hours) };

     

    The interesting part here is that you are grouping by two key conditions.  Yet a LINQ group by operator can only group by one condition.  So what you have to do is combine the conditions into a single anonymous type.  Once you've grouped, you now have a new variable 'g' instead of 's'.  Each instance of 'g' contains one of the group key values and a collection of the items in the group.  You can then use the Sum() operator on the collection to compute the sum over the hours. 

     

     

    Thursday, January 10, 2008 3:19 AM
  •  

                List<Schedule> schedules = new List<Schedule> {
                    new Schedule(5311,      4 ,        "1/3/08",   "1/5/2008"),
                    new Schedule(5311,      5 ,       " 1/3/08 ",   "1/5/2008"),
                    new Schedule(9983,      1 ,        "1/3/08",   "1/5/2008"),
                    new Schedule(9983,      2 ,        "1/3/08",   "1/5/2008"),
                    new Schedule(5311,      3  ,       "1/7/08",   "1/12/2008"),
                    new Schedule(5311,     7 ,        "1/8/08",   "1/12/2008")
               };

                var groupById = from s in schedules
                            group s by s.empid into g
                            select g.AsEnumerable();

                IEnumerable<IEnumerable<Schedule>> result = new List<IEnumerable<Schedule>>();

                foreach (var item in groupById)
                {
                    var groupByWeekend = from s in item
                                         group s by s.weekending into g
                                         select g.AsEnumerable();
                    result = result.Union(groupByWeekend);
                }

     

                foreach (var item in result)
                {
                    Console.WriteLine("{0} {1} {2}", item.First().empid, item.First().Date, item.Sum(s => s.hours));
                }

     

    Too complex I think.

     

    A trick one, but not always correct.

     

                var result =
                            from s in schedules
                            group s by  String.Concat("{0}_{1}", s.empid.ToString(), s.weekending.ToString()) into g
                            select g.AsEnumerable();

     

                foreach (var item in result)
                {
                    Console.WriteLine("{0} {1} {2}", item.First().empid, item.First().Date, item.Sum(s => s.hours));
                }

    Thursday, January 10, 2008 3:27 AM
  •  Matt Warren - MSFT wrote:

    Here's what your query would look like.

     

    from s in db.Schedules

    group s by new {s.empid, s.weekending} into g

    select new { g.Key.empid, g.Key.weekending, g.Sum(s => s.hours) };

     

    The interesting part here is that you are grouping by two key conditions.  Yet a LINQ group by operator can only group by one condition.  So what you have to do is combine the conditions into a single anonymous type.  Once you've grouped, you now have a new variable 'g' instead of 's'.  Each instance of 'g' contains one of the group key values and a collection of the items in the group.  You can then use the Sum() operator on the collection to compute the sum over the hours. 

     

     

     

    It's much better than mine. I'm just curious how the anonymous type compared. How it ensures these anonymous type objects are compared by their empid and weekending, while not object reference?

    • Proposed as answer by millie33 Tuesday, April 24, 2012 1:02 PM
    • Unproposed as answer by millie33 Tuesday, April 24, 2012 1:03 PM
    Thursday, January 10, 2008 3:31 AM
  •  

    Got it in MSDN.

    Because the Equals and GetHashCode methods on anonymous types are defined in terms of the Equals and GetHashcode of the properties, two instances of the same anonymous type are equal only if all their properties are equal.

    Thursday, January 10, 2008 3:37 AM
  • Thanks.  But i'm having trouble converting that to VB. 

     

    Thursday, January 10, 2008 5:00 PM
  • Ok, this is what I got for VB and it seems to work correctly...

     

    Dim q = From s In db.Schedules _

    Group s By s.empid, s.weekending Into g = Group _

    Select New With {.EmpId = empid, .weekending = weekending, _

    .hours = g.Sum(Function(s) s("hours"))}

     

    This seems to work correctly, but as I review the results all my hours are rounding.  Many hours in the original table are a decimals, such as 7.25, 8.5, 6.33, etc.

     

    When I write the results to the console (and view it in a datagridview) the hours are rounded up.  For instance, 7.75 rounds up to 9, 6.25 rounds up to 7, etc.

     

    Do I have the query correct and if so what am I doing wrong that I recieve rounded hours?

     

    Thursday, January 10, 2008 6:17 PM
  • What is s("hours")? Why isn't it s.Hours?
    Thursday, January 10, 2008 7:07 PM
  • s.hours gives me an error.

     

    I should clarify I am trying to to perform this linq query on a datatable in a dataset.  The datatable is filled using an oledb reader.

     

    Thursday, January 10, 2008 7:31 PM
  •  c_manboy wrote:

    s.hours gives me an error.

     

    I should clarify I am trying to to perform this linq query on a datatable in a dataset.  The datatable is filled using an oledb reader.

     

     

     

    If s.hours is failing how is s.empid working?

     

     Dim q = From s In db.Schedules _

    Group s By s.empid, s.weekending Into g = Group _

    Select New With {.EmpId = empid, .weekending = weekending, _

    .hours = g.Sum(Function(s) s("hours"))}

     

     

     

    Thursday, January 10, 2008 8:25 PM
  • Ok, I'm not sure what I was typing, but I redid it and it seems to be working correctly.  I've been looking at so many different examples online for sum and group (some of which are probably beta examples) that I probably combined some of them and got the error that I did.

     

    here's my code that finally works:

     

    Dim q = From s In Me.ds.Schedules _

    Group s By s.EMPID, s.WeekEnding Into g = Group _

    Select New With {.EmpId = EMPID, .WeekEnding= WeekEnding, _

    .hours = g.Sum(Function(s) s.Hours)}

     

    Thanks for all your help.
    Thursday, January 10, 2008 9:05 PM