Why is LINQ to Entities making a subquery for me?

Question

I'm using .NET 4 and the Entity Framework to construct a simple query. Here's the C# code:

return Context.Files.Where(f => f.FileHash != 40)
                    .OrderByDescending(f => f.Created)
                    .Take(5);

When I trace the query using ObjectQuery.ToTraceString(), I find the following subquery:

SELECT TOP (5)
  [Project1].[ID] AS [ID],
  -- <snip> lots of columns
  [Project1].[FileHash] AS [FileHash]
FROM ( SELECT
         [Extent1].[ID] AS [ID],
         -- <snip> lots of columns
         [Extent1].[FileHash] AS [FileHash]
       FROM [dbo].[Files] AS [Extent1]
       WHERE (LEN([Extent1].[FileHash])) <> 40
)  AS [Project1]
ORDER BY [Project1].[Created] DESC

FileHash is defined as an NVARCHAR(255).

This seems strange to me, as I don't see any need for a subquery. Why is EF doing this for me, and is there something I can do to not take what I assume is a performance hit from such a query?

Answer 1

First of all, I doubt this is worth worrying about. I'm guessing if you compare the query execution plan for what EF generates to that of an 'optimal' hand-written query, the results will be effectively identical. I would guess the only likely penalty is that the EF generated query make take a fraction of a second longer to parse. In the grand scheme of things, this is probably not worth considering.

As to why EF generates the query this way in the first place, I'm pretty sure it has to do with the inherent complexity of translating LINQ methods into valid SQL queries. The engine that performs this translation is very modular, I'm sure, and each module must generate a part of the query that can easily be incorporated into the final, complete query. Although it would certainly be possible to run a final 'optimization' pass to eliminate redundancies, there's probably very little harm in delegating that task to SQL Server itself.