CODEWITHSUNDEEP
c#mysqllinq
J Orgus
J Orgus

Reputation: 45

Convert SQL Query into C#

I have the following SQL query:

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

That query gets the count of students classes and returns the students with least classes.

How can I convert this into C#? My attempt does not yield the expected result. I have:

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

However that returns all the names of all the students registered in courses.

================================

Here are the tables:

==================
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)
---------------
==================

Final Query

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

Upvotes: 3

Views: 6091

Answers (1)

Michael Sander
Michael Sander

Reputation: 2737

EDIT: fixed statement

IEnumerable<IGrouping<Student, Student>> query = from student in ent.Students
    join studentClass in ent.StudentClasses on student.Id equals studentClass.StudentId into studentClassesJ
    from studentClass in studentClassesJ.DefaultIfEmpty()
    group student by student
    into studentGroup
    select studentGroup;

IEnumerable<Student> result = query.Where(x => x.Count() == query.Min(y => y.Count())).Select(x => x.Key);

Be aware that although you stated that your SQL query will return the students with least umber of classes, your SQL is returning only users that have no classes at all, due to HAVING count(sc.id) = 0. The LINQ statement I provided is not filtered, but only ordered by the number of classes, but you should be able to extend it with the desired filtering.

Upvotes: 1

Related Questions