question

JROW-1086 avatar image
0 Votes"
JROW-1086 asked ZhiLv-MSFT answered

Group and Sum multiple columns with a calculation Linq

Hi All,

I feel that what I am doing with my code here is really bad practice, or at least it feels that way.

I am working on a project which will be calculating peoples salary on a weekly basis, based on hours worked they will be paid overtime etc...

I am performing a join/group by then summing multiple colummns together. However, I am performing calculations inside of the group by, which feels wrong to me. I am wanting to know if theres a way of adding the summed columns together in a different manner to how I am performing it?

There are 4 different types of overtime payment in the month detailed below, which are Basic, Voluntary, extended and Other

 ```
  HoursOnBankHolidayW3 = pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.Basic : 0)
                              + pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.Voluntary : 0)
                              + pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.Other : 0)
                              + pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.ExtendedHours : 0) > 2250
                              ? pg.Sum(x => x.ShiftDate == weekThree ? x.Voluntary : 0)
                              + pg.Sum(x => x.ShiftDate == weekThree ? x.ExtendedHours : 0)
                              + pg.Sum(x => x.ShiftDate == weekThree ? x.Other : 0)
                              + pg.Sum(x => x.ShiftDate == weekThree ? x.Basic : 0) : 0,
 ```

So, the code above is being performed inside a group by query, I am checking if the summed hours in week 3 are greater than 2250 minutes (37.5) hours and if so, Sum the following values. This seems really cumbersome. and im wondering if theres a neater way to sum multiple columns and perform a calculation other than the above.

dotnet-aspnet-core-razor
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered

Hi @JROW-1086,

I am performing a join/group by then summing multiple colummns together. However, I am performing calculations inside of the group by, which feels wrong to me. I am wanting to know if theres a way of adding the summed columns together in a different manner to how I am performing it?

Without the table structure, it's hard to determine if there is a better way to achieve the same result. So, it is better to share the table structure or create a simple sample with the test data, so that we can easier to reproduce the problem and help you find a better way.

    HoursOnBankHolidayW3 = pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.Basic : 0)
                               + pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.Voluntary : 0)
                               + pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.Other : 0)
                               + pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree ? x.ExtendedHours : 0) > 2250
                               ? pg.Sum(x => x.ShiftDate == weekThree ? x.Voluntary : 0)
                               + pg.Sum(x => x.ShiftDate == weekThree ? x.ExtendedHours : 0)
                               + pg.Sum(x => x.ShiftDate == weekThree ? x.Other : 0)
                               + pg.Sum(x => x.ShiftDate == weekThree ? x.Basic : 0) : 0,

To the above code, you could try to use a where clause to filter the data, then use the Sum method to calculate the data. Code like this:

         var pglist = new List<pgTest>()
         {
             new pgTest(){ Id=1, ShiftDate= new DateTime(2022,5,16,0,0,0), Basic = 12, ExtendedHours= 12, Other=11, Voluntary=21},
              new pgTest(){ Id=1, ShiftDate= new DateTime(2022,5,17,0,0,0), Basic = 12, ExtendedHours= 12, Other=11, Voluntary=21},
               new pgTest(){ Id=1, ShiftDate= new DateTime(2022,5,18,0,0,0),  ExtendedHours= 12, Other=11, Voluntary=21},
                new pgTest(){ Id=1, ShiftDate= new DateTime(2022,5,19,0,0,0), Basic = 12, ExtendedHours= 12, Other=11, Voluntary=21},
                 new pgTest(){ Id=1, ShiftDate= new DateTime(2022,5,20,0,0,0),  ExtendedHours= 12, Other=11, Voluntary=21}
         };

         var vStart3 = new DateTime(2022,5,16,0,0,0);
         var weekThree = new DateTime(2022, 5, 20, 0, 0, 0);


         var query2 = pglist.GroupBy(c => c.Id).Select(c =>
         {
             // use a where clause filter the data first.
             var pg = c.Where(x => x.ShiftDate >= vStart3 && x.ShiftDate <= weekThree).ToList();
             return new
             {
                 ShiftDate = c.Key,
                 HoursOnBankHolidayW4 = pg.Sum(x => x?.Basic)
              + pg.Sum(x => x?.Voluntary)
              + pg.Sum(x => x?.Other)
              + pg.Sum(x => x?.ExtendedHours) > 20
              ? (pg.Sum(x => x.ShiftDate == weekThree ? x.Voluntary : 0)
              + pg.Sum(x => x.ShiftDate == weekThree ? x.ExtendedHours : 0)
              + pg.Sum(x => x.ShiftDate == weekThree ? x.Other : 0)
              + pg.Sum(x => x.ShiftDate == weekThree ? x.Basic : 0)) : 0
             };
         });

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.