SQL to LINQ Help

David Smith

13y2.4kLINQ

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted 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 <= DateTime.Now)
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 Smith13y ago
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));