Get all scored classrooms with more than 5 students

Question

I'm stuck trying to come up with Linq for a particular case. I don't want to use the names of my client's database table so I've translated the situation to your typical classroom setting.

One classroom has multiple students, or even no students. Each class may or may not be assigned a grade average. Don't worry about where the grade comes from or why it's serparate from the class.

Using Linq, I want to get all the classrooms that have more than 5 students AND have been assigned the grade average.

The objects look like this.

class Classrooms {
    Guid ClassroomId;
    string subject;
}

class Students {
    Guid Student;
    Guid ClassroomId;
}

class Score {
    Guid ClassroomId;
    int someScoreHere;
}

In SQL I look for something like this:

SELECT COUNT(*) As NumberOfStudents, ClassroomId 
FROM Students WHERE ClassroomId IN (SELECT ClassroomId FROM Score)
GROUP BY ClassroomId HAVING COUNT(StudentId) > 5 BY NumberOfStudents;

My fail Linq attempt at getting a list of the Classrooms;

Guid[] scores = (from score in db.Score select score.ClassroomId).ToArray();
List<Classroom> result = (from c in db.Classroom
                        where db.Student.Contains(c.ClassroomId))
                        group c by c.ClassroomId.... BLEEEH

Answer 1

from c in db.Classroom
where db.Score.Select(s => s.ClassroomId).Contains(c.ClassroomId)
group c by c.ClassroomId into g
where g.Count() > 5
select new { NumberOfStudents = g.Count(), ClassroomId = g.Key }