Displaying Many-to-Many relationships (Nested ListViews?)

Question

I appreciate that this may be a fairly simple question - but I'm having quite a bit of trouble (as a fledgeling ASP.NET developer). I've gained a number of ideas from SO and Google with no luck and I think I'm beginning to over-think this, it seems such a standard scenario that I believe I'm missing something obvious.

I have for example, three standard example tables - note the Many-to-Many relationship.

Students
- student_id
- forename
- surname

Courses
- course_id
- course_name

StudentCourses
- studentcourse_id
- course_id
- student_id

I'd like to display these on my ASP.NET 3.5 Web Application in a way that looks similar to (the last column being a button to allow them to edit the Student information):

Students:
#  Name          Courses                 Actions
1  Joe Bloggs    Maths, English          [Manage]
2  Arthur Sleep  English                 [Manage]
3  Andy Mann     Maths, German, French   [Manage]

The "courses" column is a list of the courses that the student is currently enrolled upon. This could be empty (not yet enrolled) or contain a list (comma delimited or a standard unordered HTML list, I'm not precious) of their currently enrolled courses. This would not be a massive list as a student can only enrol on a handful of courses at any one time.

I've tried a number of solutions, ranging from asp:Repeater's to my current favourite, the ListView that came with 3.5. I'm using LINQ to SQL as my data layer (in case it's important due to LINQs complicated support for Many-to-Many relationships).

Answer 1

You can use nested repeaters. The example below calls a secondary method in the code behind file which return a List. You can access all the course information from this. I could get it working with a method from a repository class so used the code below to get around this:

protected List<tblStudentCourses> GetStudentCoursesByStudentID(int id)
{
return studentRepository.GetStudentCoursesByStudentID(id).ToList();
}

Here is the full example:

<asp:Repeater ID="parentRepeater" runat="server" DataSourceID="myDataSource">
<HeaderTemplate>          
 Header HTML         
</HeaderTemplate>

<ItemTemplate>

    <asp:Repeater ID="availabilityRepeater" runat="server" DataSource='<%#GetStudentCoursesByStudentID(Convert.ToInt32(Eval("studentCourseID"))) %>'>
        <ItemTemplate>
            <%# Eval("tblStudentCourses.courseName")  %><br />
        </ItemTemplate>
    </asp:Repeater>

    Student Name example: <%#Eval("studentName")%>

</ItemTemplate>
<FooterTemplate>
    Footer HTML 
</FooterTemplate>

This may not be the best solution but it is a working solution so i hope it helps you

Answer 2

Quite simply, you could add a couple of new properties to your "Student" class as follows (in C#):

public IEnumerable<Course> Courses
{
    get { return CourseRepository.GetCoursesByStudentId(this.Student_Id); }
}

//Aggregate course names into a comma-separated list
public string CoursesDescription 
{
    get 
    {
        if (!Courses.Any())
            return "Not enrolled in any courses";

        return Courses.Aggregate(string.Empty, (currentOutput, c) =>
              (!String.IsNullOrEmpty(currentOutput)) ?
              string.Format("{0}, {1}", currentOutput, c.Course_Name) :  c.Course_Name );
    }  
}

Now when you'll only need one repeater (bound to your students collection), as the need for the sub-repeater is removed by the "CoursesDescription" property on the Student class.

As an aside, I would highly recommend naming your tables in the singular (i.e. you name them after what 1 row represents). Also, there is a strong case for removing the identity column from your StudentCourses table and replacing it with a composite key across Student_Id and Course_Id.

Hope this helps.