Populate Nested List of Classes from single data table c#

user_9828356 0
Viewed 342

I have 2 classes having the same table structure in DB

public class Student
{
   public int SId {get; set;}
   public string SName {get; set;}
   public List<StudentDetail> SDetail {get; set;}
}

public class StudentDetail
{
   public int StudentDetailId {get; set;}
   public int StudentId {get; set;}
   public int ExamId {get; set;}
   public string Department {get; set;}
   public string Address{get; set;}
}

The data is fetched from the SQL by joining both the tables using StudentId. Every StudentId can have multiple Student details. Now I want to populate the data in the above 2 classes. I have written the following code. Since one id have multiple details that's why I have used group by.

    var list = dataTable.AsEnumerable()
                   .Select(x => new Student
                   {
                       SId = x.Field<int>("SId"),
                       SName = x.Field<string>("SName"),
                       SDetail = new List<StudentDetail>
                       {
                            new StudentDetail
                            {
                                StudentDetailId = x.Field<int> 
                                ("StudentDetailId"),
                                ExamId = x.Field<int>("ExamId"),
                                Department = x.Field<string>("Department") 
                            }
                       }.ToList()
                   }).GroupBy(x => x.SId).Select(x => 
                      x.FirstOrDefault()).ToList();

But e.g. for Id 1 if there are 2 details then Student Detail list should have 2 records. But this code is creating two Id records having one record in Student Detail each like below

        Id -> 1 -> SId -> 1
        Id -> 1 -> SId -> 2

The data should be created like below:

        Id -> 1 -> SId -> 1
                -> SId -> 2

Any help?

lambda linq c#
1 Answers

The Select method creates one new Student for each record. For the SDetail field you create a list containing exactly one StudentDetail. When you then group by the SId field each Student still contains only one StudentDetail.

The solution is to do the grouping first. You can then think of each group representing a single Student and the elements of each group representing a StudentDetail:

var list = dataTable.AsEnumerable()
                    .GroupBy(rec => rec.Field<int>("SId"))
                    .Select(gr => new Student
                    {
                        SId = gr.Key,
                        SName = gr.First().Field<string>("SName"),
                        SDetail = gr.Select(detail => new StudentDetail
                        {
                            StudentDetailId = detail.Field<int>("StudentDetailId"),    
                            ExamId = detail.Field<int>("ExamId"),
                            Department = detail.Field<string>("Department")
                        }).ToList()
                     }).ToList();

There is another overload of the GroupBy method which combines GroupBy and Select:

var list = dataTable.AsEnumerable()
                    .GroupBy(rec => rec.Field<int>("SId"),
                             (key, gr) => new Student
                             {
                                 SId = key,
                                 SName = gr.First().Field<string>("SName"),
                                 SDetail = gr.Select(detail => new StudentDetail
                                 {
                                     StudentDetailId = detail.Field<int>("StudentDetailId"),
                                     ExamId = detail.Field<int>("ExamId"),
                                     Department = detail.Field<string>("Department")
                                 }).ToList()
                              }).ToList();
user_15418190
Related