Trying to .add to a list while already fill a list?-CodePudding

I had this question posted with too little details and I deleted it to make it more clear here, its likely that isnt possible but I want to learn if it is, and didnt find anything about it online. I got a class called (Student) And in the controller a Listof(Student) and I am filling the data from SQL server My code is

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


//going back to the controller and what I am trying to do
            List<Student> students = new List<Student>();
Connection.open();
//the command doesnt matter but I am reading data from it that has ID Name Age and SubjectID
  while (reader.Read())
            {
                students.Add(new Student() 
                {
                     StudentId = reader.GetInt32(0),
                     
                     StudentName = reader.GetString(1),
                     
                     //Here I want to fill the StudentID list, I will start typing outside of the code space 
                                      
              });
            }

I hope the general idea is clear, I want (if possible?) to fill the StudentID list with what I get from SQL there, but since I already have the command ".add" there I cant use it inside it, also not sure if its possible to put an if statement, if the student is repeated start filling the student ID with one object inside the main list to look like this Students[0]={ Id= 1, name= Zaid, SubjectID= {1,2,3..}

I hope this made everything clear, again I know there is other ways to solve this without having to fill a list inside it but I want to know if its possible

CodePudding user response：

I'll try to do that without changing your code too much.

List<Student> students = new List<Student>();
Connection.open();
Student currentStudent = null;
while (reader.Read())
{
    // First, I read all the info
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
    int subject = reader.GetString(3); // I guess the subjectID is at this index based on your post
    // If it's the first record of this student, I create its instance
    if (currentStudent?.StudentId != id)
    {
        // I add the previous student to the list
        if (currentStudent != null)
        {
            students.Add(currentStudent);
        }
        currentStudent = new Student()
        {
            StudentId = id,

            StudentName = name,
            
            SubjectID = new List<int>() // I create an empty list for the subjects
        };
    }
    // I add the subject to the list for this student
    currentStudent.SubjectID.Add(subject);
}

This should give you the result you are expecting. It can be optimized and refactored, but it's something similar to what you already wrote. Let me know if it works correctly, I didn't run it.

CodePudding user response：

You can do it with SQL side, what, i think, much better, data must be deliver to you in the way u need, without any other stuff or minimal.

 SELECT st.StudentName, st.StudentId , STRING_AGG (sub.Id, ',') as 
 subjects

 FROM Student st JOIN 
 Subject sub on s.SubjectId = sub.Id

 GROUP BY S.Id, st.StudentName, st.StudentId

So, code must look like this

students.Add(new Student() 
{
    StudentId = reader.GetInt32(0),
                     
    StudentName = reader.GetString(1),
                      
    StudentId = reader.GetString(2).Split(',').Select(int.Parse).ToList()
                            
});