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.

• Jeroen Vannevel: Where is classes defined? Can you leave out the .ToList() where you populate Classes?
• James: It should have said myClasses. TestOverviewItem.Classes is defined as an IList which is why .ToList() is there. Could be changed if it would make a difference though.
• Jeroen Vannevel: Well it depends on how the code is used but typically a .ToList() call triggers execution of the query though I'm not sure if it will actually have an effect here.

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.