How can I simultaneously group and sum a list of items using Linq?

Admin 1
Viewed 79

I'm receiving time entries from an external API and store them in a collection of type

public sealed record TimeEntry
{
    public string ActivityName { get; }
    public float HoursSpent { get; }
}

I want to group the entries by the activity, sum the hours spent and get the percentage compared to the other activities.

Given the example that group1 has 23 and group2 77 hours group 1 should have a percentage of 23% and group 2 77% because there is a total of 100 hours.

I started with this

var activitiesWithSpentHours = timeEntries
    .GroupBy(timeEntry => timeEntry.ActivityName)
    .Select(activityGroup => new
    {
        Activity = activityGroup.Key,
        HoursSpent = activityGroup.Sum(timeEntry => timeEntry.HoursSpent)
        Percentage = 0 /* calculate here */
    });

How can I calculate the percentage and add it to the return type? I have to compare the current item with the other ones in the whole list. Does Linq provide any functionality?

linq c#
4 Answers

This is a specific example of a more general problem, which is "how do you perform multiple calculations over a single sequence?" As Yair I pointed out, the simplest (and often sufficient) answer is to evaluate the sequence multiple times. In this case, you'd sum the total hours spent first, and then you'd do the GroupBy. This is, however, inefficient. If you want to do everything with a single pass, then you use Aggregate. It's the general purpose fold operation in Linq.

The code would be something like this (untested):

var activitiesWithSpentHours = timeEntries.Aggregate(
    (byName: new Dictionary<string, float> (), total: 0.0), // seed value
    (accumulated, activity) =>
    {
        accumulated.byName[activity.ActivityName] += activity.HoursSpent;
        return (accumulated.byName, accumulated.total + activity.HoursSpent);
    },
    accumulated => accmulated.byName.Select(kv => new
    {
        Activity = kv.Key,
        HoursSpent = kv.Value,
        HoursSpentPercent = kv.Value / accumulated.total
    }));
user_1144210

There is no reason you can't reference the outer table within the Select. It's not very efficient and a bit ugly but this might work:

var activitiesWithSpentHours = timeEntries
    .GroupBy(timeEntry => timeEntry.ActivityName)
    .Select(activityGroup => new
    {
        Activity = activityGroup.Key,
        HoursSpent = activityGroup.Sum(timeEntry => timeEntry.HoursSpent),
        Percentage = (activityGroup.Sum(timeEntry => timeEntry.HoursSpent) * 100) / timeEntries.Sum(timeEntry => timeEntry.HoursSpent)
    });

There would be more performant ways but that might not be a problem in your case.

user_26936740

I thik the simplest way will be to sum all hours before the group by that you did and use it on the select, like this:

var sumHoursSpent = timeEntries.Sum(timeEntry => timeEntry.HoursSpent);
var activitiesWithSpentHours = timeEntries
    .GroupBy(timeEntry => timeEntry.ActivityName)
    .Select(activityGroup => new
    {
        Activity = activityGroup.Key,
        HoursSpent = activityGroup.Sum(timeEntry => timeEntry.HoursSpent),
        HoursSpentPercent = activityGroup.Sum(timeEntry => timeEntry.HoursSpent) / sumHoursSpent 
    });

and if you dont want to sum the group twice you can do it in 2 stages:

var activitiesWithSpentHours = timeEntries
    .GroupBy(timeEntry => timeEntry.ActivityName)
    .Select(activityGroup => new
    {
        Activity = activityGroup.Key,
        HoursSpent = activityGroup.Sum(timeEntry => timeEntry.HoursSpent)
    });
var sumHoursSpent = timeEntries.Sum(timeEntry => timeEntry.HoursSpent);
var activitiesWithSpentHoursAndPercent = activitiesWithSpentHours.Select(activityGroup => new
    {
        Activity = activityGroup.Activity ,
        HoursSpent = activityGroup.HoursSpent,
        HoursSpentPercent = activityGroup.HoursSpent / sumHoursSpent
    });
user_22187130

You may find the linq let clause useful for this:

var activitiesWithSpentHours = 
  from entry in timeEntries
  group entry by entry.ActivityName into grouping
  let totalSpent = timeEntries.Sum(e => e.HoursSpent)
  let spent = grouping.Sum(e => e.HoursSpent)
  select new {
    ActivityName = grouping.Key,
    HoursSpent = spent,
    Percentage = spent / totalSpent * 100
  };

If you are open for a third-party solution, you could try linq2db's support for window functions:

var activitiesWithSpentHours = 
  from entry in timeEntries
  group entry by entry.ActivityName into grouping
  let spent = grouping.Sum(e => e.HoursSpent)
  let totalSpent = Sql.Ext.Sum(spent).Over().ToValue()
  select new {
    ActivityName = grouping.Key,
    HoursSpent = spent,
    Percentage = spent * 100 / totalSpent
  };
user_29385260
Related