Entity Framework query with grouping (many to many)

Question

Hello comrades.

I have a classical many to many scenario with three tables (students, courses, and third StudentsCourses assignment table).

I'm using EF in my new project and EF designer doesn't create third table. I need to select all cources along with number of students assigned to it. Using plain SQL is very straightforward:

select c.Id, c.Name, Count(sc.*) as StudentCount 
from Courses c left join StudentCourses sc on(c.Id=sc.CourseId)
group by c.Id, c.Name

But I can't figure out how to translate this query to Linq to SQL. Please advice.

Thank you.

Answer 1

The EF designer hides the table. It's still there, but it just creates the assocation for you, so you can just reference students from courses or vice versa.

Answer 2

You could do something like this:

var list = from c in context.Courses
           from s in c.Students
           select new
           {
             StudentName=s.Name,
             Class=s.Class,
           };

for more information look at this page