Convert SQL Query into C#

Question

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

This depends more on your data model than your database tables. For example if you have a navigation property on Students to Classes, it would be as simple as something like Student.Classes.Count() for each student - more succinctly, can you post the model classes for Student and StudentsClasses? — jdphenix
– jdphenix, Commented Apr 25, 2015 at 18:41
No need, adding orderby students.StudentsClasses.Count yielded the correct output. Thanks @jdphenix — J Orgus
– J Orgus, Commented Apr 25, 2015 at 18:47
That'll do it for sure. Navigation properties are a great thing - often times it not necessary to directly map a SQL query (concept for concept) i.e. , I see a join in the SQL, so I must need a join in the LINQ query - not true. Glad I could help. :) — jdphenix
– jdphenix, Commented Apr 25, 2015 at 18:48

Michael Sander · Accepted Answer · 2015-04-25 19:24:25Z

1

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.

edited Apr 25, 2015 at 19:24

answered Apr 25, 2015 at 18:54

Michael Sander

2,72224 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jdphenix Over a year ago

Or, with navigation properties, from student in ent.Students orderby student.Count() select student

J Orgus Over a year ago

I noticed that too, I think the way to get around that would be to get the lowest count and put it into an integer, then make that a variable and put it in the query. I am having no luck so far getting that number 0 into an integer.

Michael Sander Over a year ago

@JOrgus do i see this right? if the student with least classes has 3 classes, you want a query that returns all students with 3 classes?

J Orgus Over a year ago

CORRECTION: Yes, that is what I want on my original query, however, I got that working but with the WHERE clause explicitly stating the number 0. I have edited the original post so you can see the query. I wanted to make a query to get the number 0 and input it into the WHERE clause. @MichaelSander

Michael Sander Over a year ago

@JOrgus edited the query hope it does what you want now

Collectives™ on Stack Overflow

Convert SQL Query into C#

Final Query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Final Query

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related