Binding custom objects (List<T>) that have Sublists to a Grid by pivoting the sublist

Question

I would like to bind a List of CourseDetails List<CourseDetails> to a grid / xtragrid. Each CourseDetail has a Property StudentList of type List<Student>. The List of students should be pivoted so that the result looks like this:

My Question

How can i pivot every student in the StudentList so that i can databind a List<CourseDetails> to the same xtragrid .

 var courseList = List<CourseDetails>();
 courseList.Add(cd1);
 courseList.Add(cd2);

I have at least three problems i can not solve:

1. how to pivot the StudentList within an instance of courseDetails
2. how to UNION two courseDetail objects (cd1, cd2) / or n (n< 13000) together that for each student a seperate column is created
3. how can an object like courseDetails be bound to the xtragrid.

Demo Linqpad on Gist

A demo linqpad program can be found on gist.

Class CourseDetails

The following class represents a course in a school.

public class CourseDetails{
    public int Id{ get; set;}
    public Course Course{ get; set;}
    public Teacher Teacher{get; set;}
    public Room Room{get; set;}
    public List<Student> StudentList{get; set;}
}

List of List<CourseDetails>

Each object in a List<CourseDetails> contains a List of Students. Sometimes there are only a few students (2 to 5) in a StudentList and sometimes each StudentList has 15 to 40 students. The students between CourseDetails can be overlapping but they could also be disjunct (not intersecting / not overlapping)

The first var cd1 = new CourseDetails() contains 3 students in the List<Student>

var cd1 = new CourseDetails(){
    Id =1 
    ,Course = new Course(){CourseId = 435, CourseName="C# Ninja"}
    ,Teacher = new Teacher(){TeacherId=48, TeacherName="J Skee"}
    ,Room = new Room(){RoomId=32, RoomName="base floor R001"}
    ,StudentList = new List<Student>(){
        new Student(){Id = 101, StudentName="Amy"}
        ,new Student(){Id = 104, StudentName="Koothrap"}
        ,new Student(){Id = 105, StudentName="Cooper"}
    }
};

the second var cd2 = new CourseDetails() contains 2 students in the List<Student>

var cd2 = new CourseDetails(){
    Id =1 
    ,Course = new Course(){CourseId = 201, CourseName="SQL Basics"}
    ,Teacher = new Teacher(){TeacherId=30, TeacherName="M Gra"}
    ,Room = new Room(){RoomId=80, RoomName="2th floor R100"}
    ,StudentList = new List<Student>(){
        new Student(){Id = 101, StudentName="Amy"}
        ,new Student(){Id = 102, StudentName="Penny"}
    }
};

To make it easy the screenshot use a Tuple<string, string,....> but for the grid i would like to have each column the datatype of the underlying property (int, string, date, ...). The first line starting with Id, Course, Teacher, Amy, ... would be the header of the grid.

Answer 1

OK, I don't know if this will work, but we do something similar when we bind a grid to a list of objects, then depending on which month the user chooses, we add the days in the month as columns at runtime - and populate according to other data in the application. In my opinion, however, if you do not need to edit the data, this can be easily achieved by creating a view in your database, then simply returning the view data as a DataTable and binding directly to it. Way, way easier. Unfortunately we had to edit the data, and this was not an easy task!

Anyway, hopefully you can work your way through it.

1. You have a list of CourseDetails, and each of those has a list of Students.
2. Create you grid in the designer, and create your static columns that you will bind to the CourseDetails properties (ID,Course,Teacher,Room)
3. At runtime, set the grid's datasource to the list of course details. When/before you do this, you will have to extract the distinct Student names out of the list of CourseDetails, you could use LINQ to do this (something like [untested])-

var names = CourseDetails.SelectMany(c => c.StudentList).Select(s => s.Name).Distinct();

Add these columns as Unbound Columns to your grid, e.g.:

    //Make sure you clear existing unboundcolumns
    foreach(string name in names)
    {
       int idx = gridView.Columns.Add(new GridColumn());
       gridView.Columns[idx].Visible = true;
       gridView.Columns[idx].Tag = name;
       gridView.Columns[idx].Caption = name;
       gridView.Columns[idx].UnboundType = UnboundColumnType.Integer;
    }

Now handle the CustomUnboundColumnData event on the gridview. You will need to do something similar to this (not sure how you want to match - either ID/Name):

    CourseDetail course = e.Row as CourseDetails;
    if (e.IsGetData)
    {
        e.Value = course.StudentList.Where(s => s.Name == e.Column.Tag.ToString()).Count();  
    }

If you need to match on ID (put the ID in the Column.Tag property):

e.Value = course.StudentList.SingleOrDefault(s => s.ID == (int)e.Column.Tag).Grade;

or whatever you need to display. Obviously need to do null checks etc.

Hope that helps a bit. Still think your barking up the wrong tree, but anyway...

Answer 2

Actually you have to pivot in C# anyway because when you want bind (or feed it in anyway) your data to your UI - and your view object is Course (which I do not know is your domain object too or not; or are you using some ORM or not) - the extracted pivot data from database does not (directly) helps (is orchestrating your UI) and you should perform some LINQ group by operations to extract a list of Course.