需要帮助转换SQL到LINQ -左连接与计数

本文关键字：连接 LINQ 帮助 转换 SQL | 更新日期: 2023-09-27 18:01:30

我需要帮助将以下SQL查询转换为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}                
       );

提前感谢!

需要帮助转换SQL到LINQ -左连接与计数

为了进行左连接，您需要使用DefaultIfEmpty()方法，如下所示:

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

我做了一些研究并找到了一个解决方案。下面的表达式解决了这个问题:

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();
相关文章：