CODEWITHSUNDEEP
linqsum
andrelange91
andrelange91

Reputation: 1148

linq sum of multiple values

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.

Upvotes: 0

Views: 1043

Answers (3)

Prakash Bhatt
Prakash Bhatt

Reputation: 85

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));

        }

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205629

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();

Upvotes: 2

Adil Mammadov
Adil Mammadov

Reputation: 8686

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();

Upvotes: 1

Related Questions