Need help with converting SQL to LINQ - LEFT JOIN with Count

I need help converting the following SQL q开发者_Go百科uery into LINQ:

 select s.teacherid,t.lastname,t.firstname,t.title,t.grade, count(s.TeacherID) 
    from Teacher t
    left join Student s on t.teacherid = s.teacherid
    and t.isactive = 1
    and s.isactive = 1
    group by s.TeacherID,t.lastname,t.firstname,t.title,t.grade

I tried the following but it is not returning teacher records who have no students:

    var teachers = 
       (from t in Teachers
        join s in Students on t.TeacherID equals s.TeacherID 
        into results
        where t.IsActive == true 
        from r in results                                       
        group r by new { r.TeacherID, r.Teacher.FirstName, r.Teacher.LastName, r.Teacher.Title, r.Teacher.Grade} into g
       select new  { TeacherID = g.Key.TeacherID,FirstName = g.Key.FirstName, LastName = g.Key.LastName, Title=g.Key.Title, Grade = g.Key.Grade}                
       );

Thank you in advance!

In order to do a left join you will need to use DefaultIfEmpty() method like so:

from r in results.DefaultIfEmpty()

var teachers = 
   (from t in Teachers
    join s in Students on t.TeacherID equals s.TeacherID 
    into results
    where t.IsActive == true 
    from r in results.DefaultIfEmpty()                                       
    group r by new { r.TeacherID, r.Teacher.FirstName, r.Teacher.LastName, r.Teacher.Title, r.Teacher.Grade} into g
    select new  { TeacherID = g.Key.TeacherID,FirstName = g.Key.FirstName, LastName = g.Key.LastName, Title=g.Key.Title, Grade = g.Key.Grade}                
   );

I did some research and found a solution. The following expression fixed it:

var results = Teachers
              .Where(t => t.IsActive == true)
              .Select(t => 
              {
                  TeacherID  = t.TeacherID,
                  FirstName = t.FirstName,
                  LastName = t.LastName,
                  Title = t.Title,
                  Grade = t.Grade,
                  Count = t.Students.Where(s => s.IsActive == true).Count()                 
              });   
 results.ToList().Dump();