Can I avoid a nested SQL query for a simple Linq to Entities projection?

Question

Given a simple Linq to EF (EF5) statement:

MyDBSet.Select(x => x.Column1)

The following SQL will be produced:

SELECT
[c].[Column1] AS [Column1]
FROM (SELECT 
[MyDBSet].[Column1] AS [Column1],
[MyDBSet].[Column2] AS [Column2],
...
[MyDBSet].[ColumnN] AS [ColumnN]
FROM [dbo].[MyDBSet] as [MyDBSet]) as [c]

That extra nested query which returns all the columns is really unnecessary. It could be innocuous, but I think I'm having issues with how this scales into considerably more complex queries. So: is there some way to get EF to generate SQL without this extra nested query? My linq statements are generated with expression trees, so I'd like to avoid having to use any pass-through SQL.

Answer 1

Strange.

I just tried the following in a test database in LinqPad:

Contacts.Select(x => x.Email)

Here was the SQL output:

SELECT [t0].[email]
FROM [contacts] AS [t0]

So you're correct - something in your context is causing this to select all of the columns and then project for column1. It seems like the OP on this thread had the same problem and this answer demonstrates one way to optimise this:

https://stackoverflow.com/a/13258796/201648

It's not the cleanest solution, but it will definately give you more granular control over th SQL. Another answer on the same thread gives some insight into why EF behaves in this way:

Why does Entity Framework produce a nested query? The simple answer is because Entity Framework breaks your query expression down into an expression tree and then uses that expression tree to build your query. A tree naturally generates nested query expressions (i.e. a child node generates a query and a parent node generates a query on that query).

https://stackoverflow.com/a/13258313/201648

This is a shot in the dark, but have you tried stepping through each level of the context calls using F10 and F11 to see what is generated at each point? I'm curious as to whether the sub-query is being generated early in the stack because of some sub-select that is not obvious higher in the stack. Also, what does MyDBSet.Select(x => x) generate?