LINQ to Entities query of tests for my classes, with many subqueries

Question

This MVC4 controller method, which uses EF6, is taking a long time to run (over 2 seconds). I've added verbose comments for the purpose of this post only.

public async Task<ActionResult> Index()
{
  // Get a list of all the classes I teach.
  var myClasses = await CurrentUser.GetClassesAsync(db);

  // Get a list of all tests...
  var tests = await db.Tests
    // Which are either authored by me or involve one of my classes...
    .Where(o => o.AuthorId == CurrentUser.Id || (o.Author.SchoolId == CurrentUser.SchoolId && o.Classes.Any(c => myClasses.Contains(c.ClassName))))
    // And for each one I want...
    .Select(o => new TestOverviewItem
    {
      // The ID, title and test status
      Id = o.Id,
      Title = o.Title,
      Status = o.Status,
      // A list of which of my classes are registered for the test
      Classes = o.Classes.Where(c => myClasses.Contains(c.ClassName)).Select(c => c.ClassName).ToList(),
      // An indication of whether any results exist for students in my classes
      AnyUploads = o.Results.Any(r => r.User.Classes.Any(c => myClasses.Contains(c.ClassName))),
      // How many unmarked results there are for students in my classes
      RemainingToMark = o.Results.Count(r => r.MarkedBy == null && r.QuestionNumber > 0 && r.User.Classes.Any(c => myClasses.Contains(c.ClassName))),
      // An indication of whether I created the test
      IsOwner = o.AuthorId == CurrentUser.Id
    })
    .OrderByDescending(o => o.Id).ToListAsync();
  return View("Index", new OverviewModel(CurrentUser, tests, myClasses));
}

There are about 50 tests in total which match the criteria, and each one has ~4 Classes and ~2000 Results. I understand that all the subqueries must be slowing it down, but I'm not sure how to optimise.

Answer 1

I bet the database does not have the indexes to process this combination of conditions, especially with a contains on a name column. You will get table scans. Break off the || (o.Author.SchoolId == CurrentUser.SchoolId... part to return a new list of AuthorIds and you can add the CurrentUser.Id so the db.Tests where condition is just using the primary key.

The idea here is to preprocess your contains to be lists of ints to make the indexes happy.