Retrieving Courses Taught by a Teacher using C# and MySQL

Question

I am working on a C# application that interacts with a MySQL database to retrieve information about teachers and the courses they teach. I have a teachers table containing information about teachers and a classes table with details about the courses, including the teacher ID associated with each course. Each teacher can teach multiple courses.

Here's my current code:

[HttpGet]
[Route("api/teacherdata/findteacher/{teacherid}")]
public Teacher FindTeacher(int TeacherId)
{
    Teacher NewTeacher = new Teacher();

    MySqlConnection Conn = School.AccessDatabase();
    Conn.Open();
    
    MySqlCommand cmd = Conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM teachers LEFT JOIN classes ON teachers.teacherid = classes.teacherid WHERE teachers.teacherid = @TeacherId;";
    cmd.Parameters.AddWithValue("@TeacherId", TeacherId);
    cmd.Prepare();
    MySqlDataReader ResultSet = cmd.ExecuteReader();

    while (ResultSet.Read()) 
    {
        // Fetch teacher information
        NewTeacher.TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
        NewTeacher.TeacherFname = ResultSet["teacherfname"].ToString();
        // ... (other teacher details)

        // Fetch course information
        NewTeacher.ClassName = ResultSet["classname"].ToString();
        // ... (other course details)
    }

    Conn.Close();
    return NewTeacher;
}

I want to modify this method to include a list of all courses taught by the teacher. How can I achieve this?

Any help would be greatly appreciated. Thank you!

Answer 1

Your existing query will have the information, but it is important to understand that JOINs in SQL will result in something called a Cartesian Product between the tables. For example if you have 1 teacher with 2 courses and join the tables, you will get 2 resulting rows:

TeacherId    TeacherFName   ClassId   CourseName
        1    John                 1   Chemistry
        1    John                 2   Physics

... as a simple example, so your code to read the data would need to account for this and not create two teachers for the two rows. So if you expect to find a single teacher loading by ID, I would suggest something like the following:

public Teacher FindTeacher(int TeacherId)
{
    Teacher? NewTeacher = null;

    // Note: Any class that is Disposable should be disposed. use using.
    using MySqlConnection Conn = School.AccessDatabase(); 
    Conn.Open();
    
    MySqlCommand cmd = Conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM teachers LEFT JOIN classes ON teachers.teacherid = classes.teacherid WHERE teachers.teacherid = @TeacherId;";
    cmd.Parameters.AddWithValue("@TeacherId", TeacherId);
    cmd.Prepare();
    MySqlDataReader ResultSet = cmd.ExecuteReader();

    while (ResultSet.Read()) 
    {
        if (NewTeacher == null) // Populate the teacher on the first row read.
            NewTeacher = new Teacher
            {
                TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
                TeacherFname = ResultSet["teacherfname"].ToString();
                // ...
            };

        // Fetch course information and add to the teacher.
        NewTeacher.Courses.Add(new Course
        {
             ClassName = ResultSet["classname"].ToString();
             // ... (other course details)
        }
    }

    Conn.Close();
    return NewTeacher;
}

Here your Teacher is initially null so when we read the first row we will load the teacher related details into a new instance. The teacher class would contain a collection of Course objects to hold the course related details. This should be auto-initialized to a new List<Course> within the Teacher class:

 public ICollection<Course> Courses { get; private set; } = new List<Course>();

Now when you are iterating through your records you can populate a course for each row.

Cartesian Products can introduce some significant costs to querying as you join more tables as not only does the # of columns increase within each row to account for all table fields, but also the total # of rows returned is the product of all related rows. One teacher record can result in dozens, hundreds, or more rows to sift through depending on how tables are joined. In many cases splitting queries is more efficient.

I would recommend learning an ORM like Entity Framework to handle all of the SQL and Cartesian Product handling to read and update data. It can trivialize a method like this to:

public Teacher FindTeacher(int teacherId)
{
    using var accessDbContext = new AccessDbContext();

    var teacher = accessDbContext.Teachers
        .Include(t => t.Courses)
        .AsNoTracking()
        .Single(t => t.TeacherId == teacherId);
    return teacher;
}

Or if you do not need every column for the teacher and course, you can easily project it down to a DTO or ViewModel class using Select to get just the columns the caller will actually need. (Recommended over passing entity classes around)