In an attempt to convert this SQL query to LINQ I have hit a problem with group by. How can I group by multiple columns that originate from multiple tables? The linq query below simply won't compile so I'm in need of the correct syntax
Original SQL Query
select LTRIM(RTRIM(e.Shortname))
Name, LTRIM(RTRIM(j.JobName))
JobName, j.JobCode,
SUM(Hours) Hours,
MAX(ce.LatestTimesheetEntry) LastTimeSubmitted
from TWCTL.TW.Postings p
join TWCTL.TW.Employees e on e.EmployeeId = p.EmployeeId
join TWCTL.TW.Jobs j on j.JobCode = p.JobCode
join CentralTimeEmployees.dbo.Employees ce on ce.CtEmployeeId = e.EmployeeId
where (e.CostCentreId = 1 or e.CostCentreId = 3)
and (p.TransactionDate >= '2012-01-01' and p.TransactionDate <= '2012-07-01')
and j.JobCode <> 'CTCIT00001'
and ce.DatabaseCode = 'CTL'
and (ce.CostCentreId = 1 or ce.CostCentreId = 3)
group by j.JobName, j.JobCode, Shortname
order by e.Shortname, j.JobName
Linq Query attempt (not working)
var model =
from p in context.Postings
join e in context.Employees on p.EmployeeId equals e.EmployeeId
join j in context.Jobs on p.JobCode equals j.JobCode
join ce in context.CentralTimeEmployees on e.EmployeeId equals ce.CtEmployeeId
where (e.CostCentreId == 5 || e.CostCentreId == 3)
&& (p.TransactionDate >= fromDate
&& p.TransactionDate <= toDate)
&& j.JobCode != "CTCIT00001"
&& ce.DatabaseCode == "CTL"
&& (ce.CostCentreId == 1 || ce.CostCentreId == 3)
group j by new {j.JobName, j.JobCode} into g1
group e by e.Shortname into g2 <- doesnt work??
select
new ProjectHoursViewModel
{Posting = p, Job = g1.Key.JobName, Employee = e, CentralTimeEmployee = ce};