I need help convert this SQL to Linq below.
SELECT tblService.FilledByID, Count(tblService.ServiceID) AS NumOfServices
FROM tblService INNER JOIN refServiceType ON tblService.ServiceType = refServiceType.ServiceTypeID
WHERE (((DatePart("ww",[ServiceDateStart]))=DatePart("ww",DLookUp("ServiceDate","sysUser"))) AND ((Year([ServiceDateStart]))=Year(DLookUp("ServiceDate","sysUser"))) AND ((tblService.ServiceStatus)<>5 And (tblService.ServiceStatus)<>7))
GROUP BY tblService.FilledByID
HAVING (((tblService.FilledByID) Is Not Null));
I am not getting the same results as the SQL above.
var query= from tblService in DataAccess.SavedServiceRequestTypeList.AsEnumerable()
join refServiceType in DataAccess.refServiceTypeList.AsEnumerable()
on tblService.ServiceTypeID equals refServiceType.ServiceTypeID
orderby tblService.FilledByID
where (tblService.ServiceDateStart.Date == DataAccess.SavedSystemUserList.Select(s => s.ServiceDate.Date).Max() &&
tblService.ServiceDateStart.Year == DataAccess.SavedSystemUserList.Select(s => s.ServiceDate.Year).Max()) &&
(tblService.ServiceStatusID != 5) || (tblService.ServiceStatusID != 7)
group new { tblService, refServiceType } by tblService.FilledByID into g
select new
{
FilledById = g.Key,
NumOfServices = g.Count(s => s.tblService.ServiceId > 0)
};