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.
classes
defined? Can you leave out the.ToList()
where you populateClasses
? \$\endgroup\$ – Jeroen Vannevel Apr 12 '15 at 12:37myClasses
.TestOverviewItem.Classes
is defined as anIList
which is why.ToList()
is there. Could be changed if it would make a difference though. \$\endgroup\$ – James Apr 12 '15 at 12:40.ToList()
call triggers execution of the query though I'm not sure if it will actually have an effect here. \$\endgroup\$ – Jeroen Vannevel Apr 12 '15 at 12:46