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

Please write properly your class declaration. It is not Public Class Student, it is public class Student. — mybirthname, Dec 01 '16 at 08:21
This is not an answer to your question, but your table structure is problematic. What happens when the duration of the course changes? Update all the values in the table? Better way would be to have separate Student, Course and StudentCourses tables, See [Database Normalization](https://en.wikipedia.org/wiki/Database_normalization) — HebeleHododo, Dec 01 '16 at 08:25
@HebeleHododo I agree, but this is the data I'm getting, not what is present in the DB , which I have no control of. — portatoriacqua, Dec 01 '16 at 08:27

mybirthname · Accepted Answer · 2016-12-01T09:03:11.240

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

1

that did it!. Thanks – portatoriacqua Dec 01 '16 at 09:30

score 0 · Answer 2 · answered Dec 01 '16 at 08:52

What I'd do in your case (but I'm not sure you'll see it as 'properly grouping') is something along the lines of

var groupedStudents = masterData.GroupBy(x => x.StudentId);
foreach (var studentGroup in groupedStudents)
{
    // new studentclass
    var student = new Student(studentGroup.First().StudentId, studentGroup.First().StudentName);
    foreach (var studentRecord in studentGroup)
    {
        student.Courses.Add(new course(studentRecord.CourseId, studentRecord.CourseName, studentRecord.CourseDuration);
    }

    // add the student-object to where you want to save them i.e.
    this.MyStudentList.Add(student);
}

My workaround was something similar to this. Thanks though. – portatoriacqua Dec 01 '16 at 10:26 — portatoriacqua, Dec 01 '16 at 10:26

score 0 · Answer 3 · answered Dec 01 '16 at 09:17

The problem here is that DB is poorly organized and stays far from normalized state. But you could deal with as if it were properly separated to different tables. This way you extracted Courses, Students and then aggregated them - the code below should give a clue

// get all courses
var courses = masterData.GroupBy(x => x.CourseId).Select(group => group.First())
.Select(x => new Course {CourseId = x.CourseId, CourseName = x.CourseName, ...})
.ToDictionary(x => x.CourseId);

// get all students (with empty courses for now)
var students = masterData.GroupBy(x => x.StudentId).Select(group => group.First())
.Select(x => new Student {StudentId = x.StudentId, ...})
.ToDictionary(x => x.StudentId);

// fill students with courses
foreach(var data in masterData)
{
    student[data.StudentId].Courses.Add(courses[data.CourseId]) 
}

I suppose it is a clear way which could be reused after tables normalization. Alternatively you could try to write a complicated LINQ doing all this staff by a single query

Haha. I agree, But, DB isn't the one I have control of. Thanks for the response. — portatoriacqua, Dec 01 '16 at 10:25

Group By Raw Data from DB

3 Answers3