Dotnet ef core linq performance query

Question

We are using .NET 7, EF Core, PostgreSQL.

What is the best performance for these two queries?

var details = from courseSection in courseSectionQuerable
              join course in courseQuerable on courseSection.CourseId equals course.Id
              select new
                  {
                      SectionId = courseSection.Id,
                      SectionName = courseSection.Name,
                      CourseId = course.Id,
                      CourseName = course.Name,
                      Exercises = (
                        from courseExercise in courseExerciseQuerable
                        where courseSection.Id == courseExercise.CourseSectionId
                        select new
                        {
                            CourseExerciseId = courseExercise.Id,
                            courseExercise.Order,
                            Sets = (
                                from courseExerciseSet in courseExerciseSetQuerable
                                where courseExerciseSet.CourseExerciseId == courseExercise.Id
                                select courseExerciseSet.Exercise
                            ).ToList(),
                            SetsRepetition = courseExercise.SetsRepetition,
                        }
                      ).ToList(),
                  };

and

var details2 = courseSectionQuerable
                   .Join(courseQuerable, courseSection => courseSection.CourseId, course => course.Id, (courseSection, course) => new { courseSection, course })
                   .Select(joinResult => new
                           {
                               SectionId = joinResult.courseSection.Id,
                               SectionName = joinResult.courseSection.Name,
                               CourseId = joinResult.course.Id,
                               CourseName = joinResult.course.Name,
                               Exercises = courseExerciseQuerable
                                  .Where(courseExercise => courseExercise.CourseSectionId == joinResult.courseSection.Id)
                                  .Select(courseExercise => new
                                          {
                                              CourseExerciseId = courseExercise.Id,
                                              courseExercise.Order,
                                              Sets = courseExerciseSetQuerable
                            .Where(courseExerciseSet => courseExerciseSet.CourseExerciseId == courseExercise.Id)
                            .Select(courseExerciseSet => courseExerciseSet.Exercise)
                            .ToList(),
                        SetsRepetition = courseExercise.SetsRepetition
                    })
                    .ToList()
            });

How many hits db ? Is one or multiple?

When we remove .ToList(), I get an error that can't convert IQueryable to List.

Suggest alternative queries that have better performance and are more readable.

Answer 1

Both queries will perform a single database hit because you have ToList in both queries.

Without the ToList(), it will not be executed immediately and you will be an IQueryable object that represents the query.

For the alternative approach, it's very tough with the information you have provided in the question. you could explore the option of splitting the query into multiple queries. You should use proper navigation properties. you also have to think that you need to use the ToList or IQueryable depending in your requirement and use case.