CODEWITHSUNDEEP
c#linqjoinlinq-to-sqlgroup-by
michael_clarke
michael_clarke

Reputation: 171

LINQ - Join 3 tables with group by and sum

I have the following SQL query that I am trying to translate to LINQ:

Select Employee.Name,
       ts.HoursWorked,
       People.Date 
FROM [dbo].[HoursWorked] as Employee
JOIN (SELECT [Employee], Sum([LaborTime]) as HoursWorked
      FROM [dbo].[TimeSheet]
      GROUP BY [Employee] ) ts
ON Employee.Employee = ts.Employee
JOIN [dbo].[PeopleData] People
ON Employee.Employee = People.Employee

I've tried the following however it does not return the expected results:

from ts in _context.TimeSheet
join es in _context.HoursWorked on ts.Employee equals es.Employee
join ed in _context.EmployeeDetailed on ts.Employee equals ed.Employee
group ts by ts.Employee into g
select new 
{
    Name = g.Key,
    HoursWorked = g.Sum(e => e.LaborTime),
    FirstDate = ??? //Not sure how to access es
};

Upvotes: 3

Views: 4910

Answers (3)

Muhammad Ashikuzzaman
Muhammad Ashikuzzaman

Reputation: 3153

Any local variable of linq query is not achievable after the group range variable. So you can't access the ed (ed.Date) variable as it is declared before 

group ts by ts.Employee into g

This code will work:

var result = ts in _context.TimeSheet
         group ts by ts.Employee into g
         let singleG = g.FirstOrDefault()
         join es in _context.HoursWorked  on singleG.Employee  equals  es.Employee
         join ed in _context.PeopleData on es.Employee equals ed.Employee 
         select new 
         {
          Name = g.Key,
          HoursWorked = g.Sum(e => e.LaborTime),
          FirstDate = ed.Date 
         };

Upvotes: 0

Gilad Green
Gilad Green

Reputation: 37281

Try the following query. I've changed the order of the joins just to keep it like in your sql query. The part you are missing in your attempt is to group by more than just the name because now it isn't in a nested query (you'd also have to do it if you'd try to do the sql without a nested query)

var result = from es in _context.HoursWorked
             join ts in _context.TimeSheet on es.Employee equals ts.Employee
             join ed in _context.EmployeeDetailed on es.Employee equals ed.Employee
             group ts by new { es.Name, ed.Date } into g
             select new
             {
                 Name = g.Key.Name,
                 FirstDate = g.Key.Date,
                 HoursWorked = g.Sum(e => e.LaborTime),
             };

If you want to create a query just like your sql then:

var result = from es in _context.HoursWorked
             join ts in _context.TimeSheet.GroupBy(item => item.Employee)
                                 .Select(g => new { Employee = g.Key, HourseWorked = g.Sum(item => item.LaborTime) })
             on es.Employee equals ts.Employee
             join ed in _context.EmployeeDetailed on es.Employee equals ed.Employee
             select new
             {
                 Name = es.Employee,
                 HoursWorked = ts.HourseWorked,
                 FirstDate = ed.Date
             };

And in query syntax:

var result = from es in _context.HoursWorked
             join ts in (from item in _context.TimeSheet
                         group item by item.Employee into g
                         select new { Employee = g.Key, HourseWorked = g.Sum(item => item.LaborTime) })
             on es.Employee equals ts.Employee
             join ed in _context.EmployeeDetailed on es.Employee equals ed.Employee
             select new
             {
                 Name = es.Employee,
                 HoursWorked = ts.HourseWorked,
                 FirstDate = ed.Date
             };

Upvotes: 2

Lowkey
Lowkey

Reputation: 836

One of your join clauses is a sub-query. To make things a little easier you should separate that:

var sub = from ts in _context.TimeSheet
          group ts by ts.Employee into g
          select new {
              Employee = g.Key,
              HoursWorked = g.Sum(p=>p.LaborTime)
          };

Then you can do the whole query like this:

from emp in _context.HoursWorked
join s in sub on emp.Employee equals s.Employee
join pd in _context.PeopleData on emp.Employee equals pd.Employee
select new {emp.Name, s.HoursWorked, emp.FirstDate}

Upvotes: 0

Related Questions