SQL to LINQ Help

Question

I am really struggling transferring this SQL to linq below, can someone help me

SELECT        tblService.FilledByID, SUM(refServiceType.ServiceTypeAvgHours) AS TotalHrs
FROM            (tblService INNER JOIN
                         refServiceType ON tblService.ServiceType = refServiceType.ServiceTypeID)
WHERE        (tblService.ServiceStatus <> 5) AND (tblService.ServiceStatus <> 7) AND (tblService.ServiceDateStart BETWEEN NOW() AND NOW() - 30)
GROUP BY tblService.FilledByID

Vulpes · Answer

Try this: var query = from tblService in dataContext1 join refServiceType in dataContext2 on tblService.ServiceType equals refServiceType.ServiceTypeID into j1 where (tblService.ServiceStatus != 5) && (tblService.ServiceStatus != 7) && (tblService.ServiceDateStart >= DateTime.Now.AddDays(-30)) && (tblService.ServiceDateStart where j1.Any() from j2 in j1 group j2 by tblService.FilledByID into g select new { FilledByID = g.Key, TotalHrs = g.Sum(refServiceType => refServiceType.ServiceTypeAvgHours)};

David Smith · Answer

Yes sir that works, is there a linq app i can use to try all of my linq out, that way i dont have to keep debugging through visual studio. It will be faster outside of visual studio. I have another sql query, I almost got the hang of the group and joining in linq, how would I 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));