Entity Framework Many-To-Many + Count

Question

I have three tables, Professors, ProfessorStudent, Student.

I want all Professors + How many Students each Professor have.

I can do this:

context.ProfessorSet.Include("Student")

context.ProfessorSet.Include("Student").ToList() will read all three tables.

But i dont wanna get Student table, I want that Linq just get "Professor Table"+ "Count(*) ProfessorStudent Group By StudentId".

Its possible?

Answer 1

from p in context.ProfessorSet
from s in p.Student
group s by s.StudentId into g
select new
{
    Professor = p,
    StudentCounts = from sc in g
                    select new
                    {
                        StudentId = sc.Key,
                        Count = sc.Group.Count()
                    }
}

Answer 2

I do using this:

        var c = from tag in contexto.ProfessorSet
                select new
                {
                    Tag = tag,
                    Count = tag.Student.Count
                };

But generate this SQL:

SELECT C.Id, C.Nome, C.C1 FROM (SELECT A.Id, A.Nome, (SELECT COUNT(0) FROM ProfessorStudant AS B WHERE A.Id = B.ProfessorId ) AS [C1] FROM Professor AS A)

I want this:

Select A.Id, Count(0) from Professor A inner join ProfessorStudent B on A.Id = B.ProfessorId Group By A.Id