4
\$\begingroup\$

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?

\$\endgroup\$

2 Answers 2

2
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ Both queries are executed by for loops in the view. I guess given the fact that the queries are being triggered separately would cause multiple calls to the database, but even if I moved the setter within the code from what I can still makes multiple calls to the database. \$\endgroup\$ Feb 26, 2015 at 19:06
  • \$\begingroup\$ If the setter of Instructors uses a for..each loop this is the only occasion where a trip to the database is made. By setting viewModel.Courses, also if it uses a for..each loop, no additional trip to the database is made. \$\endgroup\$
    – Heslacher
    Feb 27, 2015 at 7:33
0
\$\begingroup\$

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);
    }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.