Entity framework: query executing 'select from' for no reason

Question

I'm having some issues with the entity framework. I'm executing a simple select from a view in the database. However, when I view the SQL that EF generates, it is executing the query twice using a select from. Is this the way it is supposed to operate? It seems very inefficient.

var reads = (from rt in ctx.C2kReadsToTransfer
                     where rt.ReadDt > fromDate
                     && rt.ReadDt < toDate
                     select rt);

This gets translated into the following SQL

SELECT 

 [Extent1].[AMRID] AS [AMRID]
 , [Extent1].[Comments] AS [Comments]
 , [Extent1].[ExternalSystemType] AS [ExternalSystemType]
 , [Extent1].[LastReadDt] AS [LastReadDt]
 , [Extent1].[ReadDt] AS [ReadDt]
 , [Extent1].[Reading] AS [Reading]
 , [Extent1].[Units] AS [Units]
 , [Extent1].[Transferred] AS [Transferred]



 FROM 
     (SELECT 
      [ReadsToTransfer].[AMRID] AS [AMRID]
      , [ReadsToTransfer].[Comments] AS [Comments]
      , [ReadsToTransfer].[ExternalSystemType] AS [ExternalSystemType]
      , [ReadsToTransfer].[LastReadDt] AS [LastReadDt]
      , [ReadsToTransfer].[ReadDt] AS [ReadDt]
      , [ReadsToTransfer].[Reading] AS [Reading]
      , [ReadsToTransfer].[Transferred] AS [Transferred]
      , [ReadsToTransfer].[Units] AS [Units]
      FROM [dbo].[ReadsToTransfer] AS [ReadsToTransfer])
    AS [Extent1]

That seems to be very inefficient, especially when the table contains close to 250 million rows as ours does. Also, if I tack a .Take(2000) onto the end of the code, it simply puts a 'select top 2000' on only the first select. Thus, making it select the top 2000 of the inside select which is the entire table.

Any thoughts on this?

Answer 1

That seems to be very inefficient

I don't think so... the outer SELECT is just a projection (actually an identity projection) of the inner SELECT, and a projection has a negligible performance impact...

Regarding the TOP 2000 clause, the fact that it is on the outer SELECT doesn't mean that the DB will read all rows from the inner SELECT ; it will read them as long as they are requested by the outer SELECT, then stop.

Just try to run the query manually, with or without the outer SELECT : I bet you won't find any significant difference in performance.