How do I get this sql to linq? Multiple groups

Question

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

Answer 1

I know that you are asking a specific, but there are a few tools out there you might be able to take advantage of to learn SQL to LINQ. Check them out and see if they help! Run your SQL query through the first tool and see what it comes up with.

Convert SQL to LINQ

LinqPad