Eager load additional data about some instructors

Question

Below is a snippet of code of a little practice project. I am still pretty new to EF and I was wondering if there is a way to write the below statement so that all required data is eager loaded since I have the ID parameter.

public ActionResult Index(int? id)
    {
        var viewModel = new InstructorIndexData
        {
            Instructors = _db.Instructors
                .Include(i => i.OfficeAssignment)
                .Include(i => i.Courses.Select(c => c.Department))
                .OrderBy(i => i.LastName)
        };

        if (id != null)
        {
            ViewBag.InstructorId = id.Value;
            viewModel.Courses = viewModel.Instructors.Single(i => i.InstructorId == id.Value).Courses;
        }

        return View(viewModel);
    }

When this action runs it grabs the instructor data and includes a few other collections because they are required for the initial view of the page. After selecting an instructor the page comes back with the instructor and the course the instructor is assigned to but this requires two calls to the database. Is there a way I can simple add the course to the initial query before it goes out to pull the instructor since that doesnt occur till the return?

Answer 1

The query to the database wont't be executed by writing this linq statement

var viewModel = new InstructorIndexData
{
    Instructors = _db.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.Courses.Select(c => c.Department))
        .OrderBy(i => i.LastName)
};  

if the InstructorIndexData.Instructors property setter isn't executing any of the following actions

• enumerating the elements by a for..each loop
• calling ToArray(), ToDictionary() or ToList() on the IEnumerable
• calling a linq operator like First(), Any

See: https://msdn.microsoft.com/en-us/data/jj573936.aspx

So, if you call ToList() on the enumerable shown above, only one call to the database will be made.

 var viewModel = new InstructorIndexData
 {
     Instructors = _db.Instructors
         .Include(i => i.OfficeAssignment)
         .Include(i => i.Courses.Select(c => c.Department))
         .OrderBy(i => i.LastName)
         .ToList()
 };  

The call to

viewModel.Courses = viewModel.Instructors.Single(i => i.InstructorId == id.Value).Courses;  

will then operate on the retrieved data and won't need a trip to the database anymore.

Answer 2

It's hard to tell without seeing your view and viewmodel but do you actually need courses for the non-current instructor. If not it would probably be more performant to not load those for all instructors and do a _db.SingleOrDefault on the selected instructor and include the courses. This is because the courses subquery for each instructor is likely more expensive than two database calls.

Also doing db context queries in your controller is bad practice because then your controller isn't unit testable, it's only integration testable (requires database connection).

I would suggest a repository pattern (note I am creating and disposing of the context for each request, this way the objects are populated from the data layer). I also used async/await for better server handeling of data fetches.

public interface IInstructorRepository
{
   Task<IEnumerable<Instructor>> GetInstructors();
   Task<Instructor> GetInstructor(int id);
}
public class InstructorRepository
{
   public Task<IEnumerable<Instructor>> GetInstructors()
   {
         using(var context = new EntityContext())
         {
            return await context.Instructors.OrderBy(i => i.LastName).ToArrayAsync();
         }
   }
   public Task<Instructor> GetInstructor(int id)
  {
      using(var context = new EntityContext())
      {
        return await context.Instructors.SingleOrDefaultAsync(i => i.InstructorId = id);
      }
   }
}

Then your controller:

public InstructorController : Controller
{
    private IInstructorRepository Repository{get;set;}
    public InstructorController(IInstructorRepository repository)
    {
       this.Repository = repository;
    }
    public InstructorController() : this(new InstructorRepository()){}
    public Task<ActionResult> Index(int? id)
    {
        var instructors = await this.Repository.GetInstructors();
        var viewModel = new InstructorIndexData()
        {
           Instructors = instructors;
        };
        if(id != null)
        {
            viewModel.InstructorId = id.Value; //place InstructorID on the view model for strong typing and intellisense in your view.
            viewModel.SelectedInstructor = await this.Repository.GetInstructor(id.Value); // do viewModel.SingleOrDefault(i => i.InstructorId == id.Value) if you need courses loaded on your non-selected instructors

        }
        return View(viewModel);
    }
}