I'm trying to fetch data from the database and export the data to a csv, I can achieve that, but my concern is having code that goes to the database within a for each loop, to get the data that has a relationship with the record I have already. What is the best way to implement this to avoid trips to the database within a foreach loop?

    public async Task<FileResult> FetchAllStudents(int SchoolId)
    {
        var studentList = await student.GetAll(SchoolId);
        if (studentList != null)
            {
                StringBuilder sb = new();
                
                //Append new line character.
                sb.Append("\r\n");
               
                foreach (var item in studentList )
                 {
                       //Get address of each student in the list
                        var studentAddress = await address.GetAddressByStudentId(item.StudentId);

                        var addressItem = studentAddress.Where(m => m.IsPrimary = true).FirstOrDefault();

                        var data = $"{addressItem.AddressLine1},{item.StudentName}";

                        //Append data with comma(,) separator.
                         sb.Append(data);
             
                 }

                 return File(Encoding.ASCII.GetBytes(sb.ToString()), "text/csv", "file.csv");
            }
       return null;
    }

CodePudding user response：

Sound like you want to fetch data in once and you get all the data without to query muiltple time to database. It is great to implement performance of an application. My idea as follow:

1. Get list of id of student.
2. Define a function inside the address services to such as "GetListOfStudentByIds" fetching data by list id of student. You can find out Any in Linq to working with array.
3. You dont need this line, ** var addressItem = studentAddress.Where(m => m.IsPrimary = true).FirstOrDefault(); ** it makes application slower. You can add conditional while fetch data from GetListOfStudentByIds.

Thanks