CODEWITHSUNDEEP
c#linq
JeeShen Lee
JeeShen Lee

Reputation: 3816

Group and Aggregate with LINQ

I have a timesheet entries as below

ActivityCode : Duration

What is the correct way to group the projects and aggregate their total time spent? i'm trying the below

    var summary = from entry in DbSet
                  where entry.Timesheet.UserID == userid &&
                  entry.Timesheet.DateSubmitted >= startdate &&
                  entry.Timesheet.DateSubmitted <= enddate
                  group entry by entry.ActivityCode.ActivityCode1
                      into groupEntry
                      select new TimeSheetSummary()
                      {
                          ActivityCode = groupEntry.Key,
                          HourSpent = Convert.ToInt32(groupEntry.Sum(x => x.Duration)),
                          Percentage = (Convert.ToInt32(groupEntry.Sum(x => x.Duration)) / 8) * 100,
                          MinuteSpent = Convert.ToInt32(groupEntry.Sum(x => x.Duration)) * 60,
                      };

Upvotes: 0

Views: 1768

Answers (2)

JeeShen Lee
JeeShen Lee

Reputation: 3816

Here's what i found as answer based on the inputs by others in the thread. thanks!

    var groupEntries = from entry in DbSet
                       where entry.Timesheet.UserID == userId &&
                             entry.Timesheet.TimeSheetDate <= endDate.Date &&
                             entry.Timesheet.TimeSheetDate >= startDate.Date
                       group entry by entry.ActivityCode
                           into groupEntry
                           select new
                           {
                               ActivityCode = groupEntry.Key,
                               Duration = Convert.ToInt16(groupEntry.Sum(r => r.Duration))
                           };

    var totalDuration = groupEntries.Sum(r => r.Duration);

    var result = from groupEntry in groupEntries
                 select new TimeSheetSummary()
                            {
                                ActivityCode = groupEntry.ActivityCode,
                                HourSpent = groupEntry.Duration / 60,
                                MinuteSpent = groupEntry.Duration % 60,
                                Percentage = groupEntry.Duration / totalDuration * 100
                            };

Upvotes: 0

scott.korin
scott.korin

Reputation: 2597

If you want to get a percentage, get a count of the number of activites and use that to divide by.

Not sure if you will need a divide by 0 check here. I'm not sure what the second LINQ will do if there's no data to begin with. Not sure if that would raise an error or not.

Int32 numberOfProjects = (from entry in DbSet
              where entry.Timesheet.UserID == userid &&
                  entry.Timesheet.DateSubmitted >= startdate &&
                  entry.Timesheet.DateSubmitted <= enddate
              select entry.ActivityCode.ActivityCode1).Distinct().Count();

var summary = from entry in DbSet
              where entry.Timesheet.UserID == userid &&
              entry.Timesheet.DateSubmitted >= startdate &&
              entry.Timesheet.DateSubmitted <= enddate
              group entry by entry.ActivityCode.ActivityCode1
                  into groupEntry
                  select new TimeSheetSummary()
                  {
                      ActivityCode = groupEntry.Key,
                      HourSpent = Convert.ToInt32(groupEntry.Sum(x => x.Duration)),
                      Percentage = (Convert.ToInt32(groupEntry.Sum(x => x.Duration)) / numberOfProjects) * 100,
                      MinuteSpent = Convert.ToInt32(groupEntry.Sum(x => x.Duration)) * 60,
                  };

Upvotes: 1

Related Questions