CODEWITHSUNDEEP
c#entity-frameworklinq
DBoi
DBoi

Reputation: 687

Counting occurrences in a many to many table with Entity framework and LINQ

I have a many to many table that links Students and Courses. This table is called StudentCourses. How can i count the number of occurrences in this table based on the course ID to get the number of students that have a particular course. I'm using Entity Framework and LINQ.

So far all I can think is that i have to go through all the students and loop over all their courses, something like:

public int numberOfCourseStudents(int courseId) {
   var students = ApplicationDbContext.Students.Include("Courses").ToList();
   var count;

   for (int i = 0; i < students.Length; i++) {
      if (students.Courses.Id == courseId) {
        count ++;
      }
   }

   return count;  
}

This seems silly though as i have a this StudentCourses Table and all I need to do is match the courseId with any course in there and then count that and that will give me the int I require.

Is there an simpler / more effective way to do this?

Just doesn't feel right to me.

Thanks

Upvotes: 1

Views: 469

Answers (1)

Jota.Toledo
Jota.Toledo

Reputation: 28464

I can think of 2 approaches:

  • Find the matching course, count the number of students in it
  • Count the number of students with a course that matches a given Id.

1st approach:

public int CountCourseStudents(int courseId) {
   // Added include in order to avoid lazy-load of students prop
   var course = context.Courses.Include("Students").Find(courseId);
   if(course == null) throw new InvalidOperationException("No matching course found");

   return couse.Students.Count;  
}

2nd approach:

public int CountCourseStudents(int courseId) {
   var courseStudents = context.Students
                   .Where(student => student.Courses
                          .Any(course => course.Id == courseId));
   return courseStudents.Count();  
}

Upvotes: 3

Related Questions