实体框架多对多+计数

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.

context.ProfessorSet.Include(“Student”)。ToList()将读取所有三个表。

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

但我不想得到学生表,我希望Linq得到“教授表”+“伯爵(*)教授学生组由StudentId”。

Its possible?

2 个解决方案

#1

I do using this:

我这样做:

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

But generate this SQL:

但是生成这个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)

SELECT C.Id,C.Nome,C.C1 FROM(SELECT A.Id,A.Nome,(SELECT COUNT(0)FROM ProfessorStudant AS B,其中A.Id = B.ProfessorId)AS [C1]来自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

从A. Ad = B.ProfessorId Group A.Id选择A.Id,Count(0)教授A内部加入教授学生B

#2

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()
                    }
}

#1