How do I get this sql to linq? Multiple groups
- by Dwight T
For a db person, LINQ can be frustrating. I need to convert the following SQL into Linq.
SELECT COUNT(o.objectiveid), COUNT(distinct r.ReviewId), l.Abbreviation
FROM Objective o
JOIN Review r
on r.ReviewId = o.ReviewId
and r.ReviewPeriodId = 3
and r.IsDeleted = 0
JOIN Position p
on p.PositionId = r.EmployeePositionId
and p.DivisionId = 2
JOIN Location l
on l.LocationId = p.LocationId
GROUP BY l.Abbreviation
The group by nested example might be the way I have to go, but not sure.
Doing one group by I have used the following code:
var query = from rev in db.Reviews
.Where(r => r.IsDeleted == false && r.ReviewPeriodId == reviewPeriodId)
from obj in db.Objectives
.Where(o => o.ReviewId == rev.ReviewId && o.IsDeleted == false)
from pos in db.Positions
.Where(p => rev.EmployeePositionId == p.PositionId && p.IsDeleted == false && p.DivisionId == divisionId )
from loc in db.Locations
.Where(l => pos.LocationId == l.LocationId)
group loc by loc.Abbreviation into locgroup
select new ReportResults
{
KeyId = 0,
Description = locgroup.Key,
Count = locgroup.Count()
};
return query.ToList();
What is the correct way?
Thanks
