portatoriacqua portatoriacqua - 1 year ago 107
C# Question

Group By Raw Data from DB

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
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

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}
StudentId: 2
StudentName: Y
List<Course> Courses: [0] - { CourseId: C1, CourseName = Cse1, CourseDuration = 2}
[1] - { CourseId: C4, CourseName = Cse4, CourseDuration = 5}


Answer Source

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;


        return course;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download