将其转换为linq或lambda c＃

Can someone please help me convert this SQL to linq or lambda c#

有人可以帮我把这个SQL转换成linq或lambda c#

select  
    count(s.ClassId) [StudentInClass], c.Name [Class], t.Name [teacher]
from 
    [dbo].[Students] s
inner join 
    class c on s.ClassId = c.Id
inner join Teacher t 
    on t.Id = c.TeacherId
group by 
    s.ClassId, c.Name, t.Name

so far this is what I have, and i am messing it up. I want to achieve the same results as in my sql query

到目前为止,这就是我所拥有的,而且我正在弄乱它。我希望获得与我的SQL查询相同的结果

        SchoolEntities db = new SchoolEntities();

        var StudentsByCourseId = from s in db.Students
            join c in db.Classes on s.ClassId equals c.Id
            join t in db.Teachers on c.TeacherId equals t.Id
            group c by s.ClassId
            into g
            select g;

in SQL this is what my reults look like, it counts the students in a class by the teacher

在SQL中,这就是我的结果,它是老师在课堂上对学生进行计数的重要因素

 
          StudentCount  Class        Teacher
          1             Geography    Teacher1
          1             Biology      Teacher1
          2             Maths        Teacher2

1 个解决方案

#1

You can use an anonymous class to group by multiple properties.

您可以使用匿名类按多个属性进行分组。

 var StudentsByCourseId = from s in db.Students
            join c in db.Classes on s.ClassId equals c.Id
            join t in db.Teachers on c.TeacherId equals t.Id
            group s by new { s.ClassId, Class = c.Name, Teacher = t.Name } 
            into g
            select new
            {
              StudentInClass = g.Count(),
              g.Key.Class,
              g.Key.Teacher,
            };