linq sum of multiple values

Question

i need to get the sum of billableHours and nonBillableHours.

this is my code.

var currentMonth = 10;

var userQuery =
            from timeEntry in TimeEntries
            join ta in Tasks on timeEntry.TaskID equals ta.TaskID
            where timeEntry.DateEntity.Month == currentMonth && timeEntry.DateEntity.Year == DateTime.Today.Year
            select new
            {
                HoursEntered = timeEntry.HoursEntered,
                Billable = ta.Billable
            };

            var localrows = userQuery.ToList();
            var grouping = localrows.GroupBy(x => x.Billable);

            var userList = grouping.Select(q => new 
            {
                billableHours = q.Where(x=> x.Billable == true),
                nonBillableHours = q.Where(x=> x.Billable != true)
            });

i cannot seem to find a way to get the sum. I need the sum of those two columns, so i can call them, and calculate values i get from them.

Ivan Stoev · Accepted Answer · 2016-10-05 07:00:36Z

2

When you need more than one aggregate, you can still get the result with a single query by using group by constant technique. Which in this specific case can be combined with conditional Sum:

var hoursInfo =
    (from timeEntry in TimeEntries
     join ta in Tasks on timeEntry.TaskID equals ta.TaskID
     where timeEntry.DateEntity.Month == currentMonth && timeEntry.DateEntity.Year == DateTime.Today.Year
     group new { timeEntry.HoursEntered, ta.Billable } by 1 into g
     select new
     {
         BillableHours = g.Sum(e => e.Billable ? e.HoursEntered : 0),
         NonBillableHours = g.Sum(e => !e.Billable ? e.HoursEntered : 0),
     }).FirstOrDefault();

answered Oct 5, 2016 at 7:00

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adil Mammadov · Accepted Answer · 2016-10-05 06:28:00Z

1

You do not need to group them. Try this query:

var userQuery =
    from timeEntry in TimeEntries
    join ta in Tasks on timeEntry.TaskID equals ta.TaskID
    where timeEntry.DateEntity.Month == currentMonth 
        && timeEntry.DateEntity.Year == DateTime.Today.Year
    select new
    {
        HoursEntered = timeEntry.HoursEntered,
        Billable = ta.Billable
    };

var billableHours = userQuery
    .Where(m => m.Billable) // Billable
    .Select(m => m.HoursEntered)
    .DefaultIfEmpty(0)
    .Sum();

var nonBillableHours  = userQuery
    .Where(m => !m.Billable) // Non-bilable
    .Select(m => m.HoursEntered)
    .DefaultIfEmpty(0)
    .Sum();

answered Oct 5, 2016 at 6:28

Adil Mammadov

8,7164 gold badges35 silver badges65 bronze badges

6 Comments

andrelange91 Over a year ago

works great. although defaultifempty, returns an error of overload not supported in linqpad.

Adil Mammadov Over a year ago

I am glad that it helped. What is type of HoursEntered?

andrelange91 Over a year ago

it is a double.

Adil Mammadov Over a year ago

@andrelange91, try using like DefaultIfEmpty() with no parameters. Please let me know the result

Adil Mammadov Over a year ago

@andrelange91, I am not familiar with linqpad so I cannot help. It is not necessary, but it is good practice to use it, beacuse when your query will not return any data Sum method will throw exception.

|

Prakash Bhatt · Accepted Answer · 2016-10-05 07:12:44Z

var currentMonth = 10;
        var TimeEntries = new List<TimeEntry>() { 
        new TimeEntry(){TaskID = 1,DateEntity = DateTime.Now.AddDays(1),HoursEntered =2},
        new TimeEntry(){TaskID = 2,DateEntity = DateTime.Now.AddDays(2),HoursEntered =3},
        new TimeEntry(){TaskID = 3,DateEntity = DateTime.Now.AddDays(3),HoursEntered =2},
        new TimeEntry(){TaskID = 4,DateEntity = DateTime.Now.AddDays(4),HoursEntered =4},
        new TimeEntry(){TaskID = 5,DateEntity = DateTime.Now.AddDays(5),HoursEntered =2},
        new TimeEntry(){TaskID = 6,DateEntity = DateTime.Now.AddDays(6),HoursEntered =6}
        };

        var UserTasks = new List<UserTask>(){
               new UserTask(){TaskID = 1,Billable = true} ,
               new UserTask(){TaskID = 2,Billable = false} ,
               new UserTask(){TaskID = 3,Billable = true} ,
               new UserTask(){TaskID = 4,Billable = false} ,
               new UserTask(){TaskID = 5,Billable = true} ,
               new UserTask(){TaskID = 6,Billable = false} 
        };

        var userQuery =
                    from x in
                        (from timeEntry in TimeEntries
                         join ta in UserTasks on timeEntry.TaskID equals ta.TaskID
                         where timeEntry.DateEntity.Month == currentMonth && timeEntry.DateEntity.Year == DateTime.Today.Year
                         select new
                         {
                             HoursEntered = timeEntry.HoursEntered,
                             Billable = ta.Billable
                         })
                    group x by x.Billable into g
                    select new
                    {
                        IsBillable = g.Key,
                        Billabe = g.Where(t => t.Billable == true).Sum(x => x.HoursEntered),
                        NonBillable = g.Where(t => t.Billable == false).Sum(x => x.HoursEntered)
                    };

        foreach (var item in userQuery.ToList())
        {
            Console.WriteLine(string.Format("{0} - {1}", item.IsBillable? "Billable":"Non-Billable",item.IsBillable?item.Billabe:item.NonBillable));

        }

Collectives™ on Stack Overflow

linq sum of multiple values

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related