Entity Framework Search for Enum pulls back all data

Question

I'm executing the following query using entity framework, I'd like to filter my results by the TakerStatus which is an Enum:

var takerCount = this.DbContext.Set<Quiz>().Single(x => x.UrlId == urlId).Takers.Count(x => x.TakerStatus == TakerStatus.Abandoned || x.TakerStatus == TakerStatus.Complete)

Here's the enum:

public enum TakerStatus
{
    /// <summary>
    /// Taker is currently in the process of completing the quiz
    /// </summary>
    InProgess = 0,

    /// <summary>
    /// Taker failed to complete the quiz and was marked as abandoned
    /// </summary>
    Abandoned = 1,

    /// <summary>
    /// Taker successfully completed taking the quiz
    /// </summary>
    Complete = 2
}

I realised that this query was running slowly and I wanted to know why, there are a good number of records in there, but I wanted to improve the speed by adding indexes.

But after examining the query that is actually produced by this, I see that it appears to be ignoring the lambda expression...

SELECT
`Extent1`.`Id`, 
`Extent1`.`QuizId`, 
`Extent1`.`QuizVersionId`, 
`Extent1`.`UserId`, 
`Extent1`.`TakerName`, 
`Extent1`.`CurrentQuestionNumber`, 
`Extent1`.`CurrentQuestionStartTime`, 
`Extent1`.`CurrentScore`, 
`Extent1`.`Completed`, 
`Extent1`.`IpAddress`, 
`Extent1`.`StartTime`, 
`Extent1`.`FinishTime`, 
`Extent1`.`Abandoned`, 
`Extent1`.`PasswordId`, 
`Extent1`.`PersonalityResultId`, 
`Extent1`.`PercentageResult`, 
`Extent1`.`Status`, 
`Extent1`.`TakerStatus`
FROM `Taker` AS `Extent1`
 WHERE `Extent1`.`QuizId` = 330

It's selecting every taker for the quiz but not even examining the TakerStatus column.

What gives? I wanted to add an index to TakerStatus to speed it up

Answer 1

Try this, I gues it needs a pair of braces around the condition. I think its an operator precedence issue.

var takerCount = this.DbContext.Set<Quiz>().Single(x => x.UrlId == urlId).Takers.Count(x => (x.TakerStatus == TakerStatus.Abandoned || x.TakerStatus == TakerStatus.Complete))

Answer 2

Single enumerates your IQueryable and triggers the query (the rest of the processing being done in application server memory, on the whole resultset)

I would try :

var takerCount = this.DbContext
    .Set<Quiz>()
    .Where(x => x.UrlId == urlId)
    .Select(o=>o.Takers.Count(x => x.TakerStatus == TakerStatus.Abandoned || x.TakerStatus == TakerStatus.Complete))
    .FirstOrDefault();