Dfyjkt

I am going through this tutorial to help me better understand the EF Structure. I currently use SQL.

https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/read-related-data?view=aspnetcore-2.1&tabs=visual-studio

In this example, it shows the instructor, office, student, course, grade, and assignments

 public async Task OnGetAsync(int? id, int? courseID)
 
 Instructor = new InstructorIndexData();
 Instructor.Instructors = await _context.Instructors
 .Include(i => i.OfficeAssignment)
 .Include(i => i.CourseAssignments)
 .ThenInclude(i => i.Course)
 .ThenInclude(i => i.Department)
 .Include(i => i.CourseAssignments)
 .ThenInclude(i => i.Course)
 .ThenInclude(i => i.Enrollments)
 .ThenInclude(i => i.Student)
 .AsNoTracking()
 .OrderBy(i => i.LastName)
 .ToListAsync();

 if (id != null)
 
 InstructorID = id.Value;
 Instructor instructor = Instructor.Instructors.Where(
 i => i.ID == id.Value).Single();
 Instructor.Courses = instructor.CourseAssignments.Select(s => s.Course);
 

 if (courseID != null)
 
 CourseID = courseID.Value;
 Instructor.Enrollments = Instructor.Courses.Where(
 x => x.CourseID == courseID).Single().Enrollments;
 
 

To help me better understand the syntax would this SQL Statement be the equivalent?

SELECT *
FROM Instructor INNER JOIN
 OfficeAssignment ON Instructor.ID = OfficeAssignment.InstructorID INNER JOIN
 Department ON Instructor.ID = Department.InstructorID INNER JOIN
 Course ON Department.DepartmentID = Course.DepartmentID INNER JOIN
 Enrollment ON Course.CourseID = Enrollment.CourseID INNER JOIN
 CourseAssignment ON Course.CourseID = CourseAssignment.CourseID INNER JOIN
 Student ON Enrollment.StudentID = Student.ID
WHERE Instructor.ID = @ID AND Course.CourseID = @CourseID ORDER BY Instructor.Lastname

It helps to use entities as objects rather than thinking of them as tables. Yes, they typically correlate directly to the underlying tables, but that is a means to an end. You can leverage the relationships more directly than simply treating it as another way to write SQL.

For example:

 Instructor.Instructors = await _context.Instructors
 .Include(i => i.OfficeAssignment)
 .Include(i => i.CourseAssignments)
 .ThenInclude(i => i.Course)
 .ThenInclude(i => i.Department)
 .Include(i => i.CourseAssignments)
 .ThenInclude(i => i.Course)
 .ThenInclude(i => i.Enrollments)
 .ThenInclude(i => i.Student)
 .AsNoTracking()
 .OrderBy(i => i.LastName)
 .ToListAsync();

This will correspond roughly to an SQL statement with a bunch of inner joins and an OrderBy clause. In the realm of EF though, this would be considered bad practice. The reason is that like an SQL statement with inner joins, you are effectively doing a "SELECT *" across all of those tables. Do you really want all of the columns of all of the joined tables?

AsNoTracking() merely tells EF that for the data retrieved, you aren't going to modify it, so don't bother tracking dirty state. This is a performance tweak for read operations.

ToListAsync() performs the query as an awaitable operation which will free up the thread the method was called on. No magic multi-threaded execution here, just the call can hand off to SQL Server, release it's thread, then be assigned a new thread based on a continuation point after the await.

One warning sign I see with the example is the use of the null-able parameters. Can this method validly be called with:

• Neither an ID or course ID?
  and


• An ID with no course ID?
  and


• A course ID with no ID?
  and


• Both an ID and course ID?

If any of these combinations is invalid then the method should be split up or refined.

Getting back to the "SELECT *" behaviour, using EF you have a lot of power hiding behind the scenes ready to turn Linq map/reduce operations into SQL to run against the server and return you a meaningful, minimal set of data.

For example:

var query = _context.Instructors.AsQueryable();
if (id.HasValue)
 query = query.Where(i => i.ID == id.Value);
query = query.OrderBy(i => i.LastName);

var instructors = await query.Select(i => new InstructorIndexData 
 
 InstructorId = i.ID,
 // ...
 Courses = i.CourseAssignments.Select(ca => new CourseData 
 CourseId = ca.Course.ID,
 CourseName = ca.Course.Name, 
 //..
 
 ).ToListAsync()

if (courseId.HasValue)

 var enrollments = await query.SelectMany(i => i.Courses.SingleOrDefault(c => c.CourseID == courseID.Value).Enrollments.Select(e => new EnrollmentData 
 
 InstructorId = i.ID,
 EnrollmentId = e.EnrollmentID,
 CourseId = e.Course.CourseID,
 //...
 ).ToListAsync();

 // From here, group the Enrollments by Instructor ID and add them to the Instructor index data.
 var groupedEnrollments = enrollments.GroupBy(e => e.InstructorId);
 foreach(instructorId in groupedEnrollments.Keys)
 
 var instructor = instructors.Single(i => i.InstructorId == instructorId);
 instructor.Enrollments = groupedEnrollments[instructorId].ToList();
 
 

Now the caveat here is that I'm basing this on memory and with a rough guess of your structure and desired output. The key points would be leveraging the IQueryable and issuing Select statements to just pull back the exact data you need to populate the objects you want to provide to a view.

I do this in 2 query executions, one to get the instructor(s), then the second to get the enrollments if requested based on the provided course ID. Personally I'd split this into two methods since I'd expect the enrollments would be optional. Also there is a difference between fetching one instructor, and all instructors. In cases where potentially large amounts of data are returned, you should look at establishing pagination with Skip() and Take() to avoid expensive queries bogging down the CPU, network, and memory usage.