我有以下的SQL查询:
SELECT
s.name, count(sc.id) AS classes
FROM
students s
LEFT JOIN studentsclasses sc ON s.id = sc.studentid
GROUP BY s.name
HAVING count(sc.id) = 0
ORDER BY count(sc.id);
这个查询获取学生课程数量并返回选修最少的学生。
我该如何将其转换为C#?我的尝试没有产生预期结果。我有以下代码:
var query = (from students in ent.Students
join classes in ent.StudentsClasses on students.ID equals classes.StudentID into gj
from subpet in gj.DefaultIfEmpty()
select new { students.Name }).ToList();
然而,这将返回所有注册课程的学生姓名。
================================
以下是表格:
==================
StudentsClasses
----------------
ID (Registration ID of the class)
StudentID (ID of student taking class)
ClassID (ID of certain class)
----------------
==================
Students
---------------
ID (ID of student)
Name (Name of student)
GradeLevelID (Grade of student)
---------------
==================
最终查询
var query = (from students in ent.Students
join classes in ent.StudentsClasses on students.ID equals classes.StudentID into gj
from subpet in gj.DefaultIfEmpty()
orderby students.StudentsClasses.Count
where students.StudentsClasses.Count == 0
select new { students.Name }).ToList();
Student.Classes.Count()
这样简单的操作即可 - 更简洁地说,您能否发布一下Student和StudentsClasses的模型类? - jdphenixorderby students.StudentsClasses.Count
就可以得到正确的输出结果了。谢谢 @jdphenix - J Orgus