Group By Raw Data from DB

Question

This is my raw data coming from DB:

PrimaryColumn   StudentId  StudentName  CourseName  CourseId  CourseDuration
     1               1           X         Cse1       C1          2
     2               1           X         Cse2       C2          1 
     3               1           X         Cse3       C3          3
     4               2           Y         Cse1       C1          2
     5               2           Y         Cse4       C4          5

Classes from C# end:

public class Student 
{
  public int StudentId {get; set;}
  public string StudentName {get; set}
  public List<Course> Courses {get; set;}
}    

public class Course 
{
  public int CourseId {get; set;}
  public string CourseName {get; set;}
  public int CourseDuration {get; set; }
}

My goal is to fetch the data grouped by Students and the courses they would be taking which is done using List<Course> as a property of Student Class.

So, I thought the goal is pretty forward. So, I went ahead and used GroupBy on the raw data coming from DB to C# hoping to get the result but to no avail.

This is the best I've gotten, so far.

  masterData.GroupBy(x => new { x.StudentId, x.StudentName }, (key, group) => new { StudentId = key.StudentId, StudentName = key.StudentName, Courses=group.ToList() }).ToList();

Although this doesn't get me what I hope to fetch. With some minor code workaround post this call, I'm able to achieve the what I need. But, it's irking me everytime that I'm unable to group the raw data properly.

It would be really helpful if anyone could show me the right way to further optimize the above code or do it different way altogether.

This is the way I need the result to be in a List<Student>:

Student:
     StudentId: 1
     StudentName: X
     List<Course> Courses: [0] - { CourseId: C1, CourseName = Cse1, CourseDuration = 2}
                           [1] - { CourseId: C2, CourseName = Cse2, CourseDuration = 1}
                           [2] - { CourseId: C3, CourseName = Cse3, CourseDuration = 3} 
Student:
     StudentId: 2
     StudentName: Y
     List<Course> Courses: [0] - { CourseId: C1, CourseName = Cse1, CourseDuration = 2}
                           [1] - { CourseId: C4, CourseName = Cse4, CourseDuration = 5}

Cheers

Answer 1

You can do it like this:

Here full example: dotNetFiddle

List<Student> result = data.GroupBy(x => new { x.StudentID, x.StrudentName }, 
            (key, group) => new Student{ 
                                          StudentId = key.StudentID, 
                                          StudentName = key.StrudentName,                
                                          Courses = GetCourses(group)}).ToList();

    //You can do this operation with Reflection if you want. If you don't want to write manually the property names.
    public static List<Course> GetCourses(IEnumerable<RawData> data)
    {
        List<Course> course = new List<Course>();

        foreach(var item in data)
        {
            Course c = new Course();

            c.CourseDuration = item.CourseDuration;
            c.CourseId = item.CourseID;
            c.CourseName = item.CourseName;

            course.Add(c);
        }

        return course;
    }