Why doesn't Entity Framework add a "where" to the SQL generated when SingleOrDefault is used?

Question

I'm using Entity Framework CTP 5 with "code only" (with SQL Server 2008). I have an entity returned from a DbContext that I then access a child collection from, and select one item from it. Here's my LINQ statement:

Question currentQuestion = currentTopic.Questions.SingleOrDefault(x => x.IsCurrent);

This produces the following SQL:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[CreatedAt] AS [CreatedAt], 
[Extent1].[IsCurrent] AS [IsCurrent], 
[Extent1].[Xml] AS [Xml], 
[Extent1].[TopicId] AS [TopicId]
FROM [dbo].[Questions] AS [Extent1]
WHERE [Extent1].[SessionId] = 24

My "IsCurrent" restriction isn't referenced at all. IsCurrent is a bit field in my database.

Can anyone explain why this is? It's causing a huge performance hit.

Answer 1

That is by design in all EF implementations. Questions collection exposes IEnumerable<Question> not IQueryable<Question>. When you access Questions property lazy loading is triggered and all related questions are loaded. Then you call SingleOrDefault on loaded collection.

If you want just single question run this query instead:

var question = context.Questions
                 .SingleOrDefault(q => q.Session.Id == sessionId && q.IsCurrent);

Answer 2

I think because the child collection (currentTopic.Questions) is lazily loaded completely and then the LINQ to Object version of SingleOrDefault and not the LINQ to Entities one is called on your collection.

The SQL statement you posted contains WHERE [Extent1].[SessionId] = 24. That shows it's loading all Questions for your currentTopic.