Can I avoid a nested SQL query for a simple Linq to Entities projection?
Given a simple Linq to EF statement (EF5):
MyDBSet.Select(x => x.Column1)
The following SQL will be generated:
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]
This additional subquery that returns all columns is really unnecessary. It may be harmless, but I think I am having trouble with how this scales into significantly more complex queries. So: is there a way to get EF to generate SQL without this extra nested query? My linq statements are generated with expression trees, so I would like to avoid using any end-to-end SQL.
source to share
It's strange.
I just tried the following on a test database in LinqPad :
Contacts.Select(x => x.Email)
Here is the SQL output:
SELECT [t0].[email]
FROM [contacts] AS [t0]
So you are right - something in your context is causing all columns to be selected and then project for column1
. It seems that the OP in this thread has the same problem and this answer demonstrates one way to optimize this:
fooobar.com/questions/289459 / ...
This is not the cleanest solution, but it will certainly give you more granular control over SQL. Another answer in the same thread gives some insight into why EF behaves this way:
Why is Entity Framework generating a subquery? Simple answer because Entity Framework splits your query expression into and then uses that expression tree for the query. The tree naturally generates nested query expressions (i.e. A child node generates a request and the parent node generates a request for that request).
fooobar.com/questions/289459 / ...
It's a shot in the dark, but have you tried going through each level of contextual calls with F10 and F11 to see what is being generated at each point? I am wondering if the subquery occurs at the beginning of the stack due to some sub-selection that is not obvious higher up the stack. Also, what generates MyDBSet.Select(x => x)
?
source to share
as Chris Hermut mentioned, Lazy Upload your request:
IQueryable<ColumnType> query = context.MyDBSet.Select(X => X.Column1);
IEnumerable<ColumnType> result = query.ToList();
source to share