Entity Framework selects too many columns in views

Question

When I use Entity Framework against a SQL table, it only refers to the necessary columns in the generated SQL:

ctx.Types.Select(rdi => rdi.Name)

becomes

SELECT [Extent1].[Name] AS [Name]
FROM [dbo].[Types] AS [Extent1]

However, if I make an analogous query against a SQL view, Entity Framework generates SQL referring to every column in the view:

ViewTypes.Select(rdi => rdi.Name)

becomes

SELECT [Extent1].[Name] AS [Name]
FROM (SELECT 
      [ViewTypes].[Name] AS [Name], 
      ... every other column in my view ...
      FROM [dbo].[ViewReferenceDataTypes] AS [ViewReferenceDataTypes]) AS [Extent1]

I'm sure SQL Server will perform its own optimization to end up ignoring all the columns it doesn't care about, but this still results in a massive block of SQL being sent to the server. (My actual example included a join, which resulted in every column from several tables being selected...)

Is there a good reason for Entity Framework to do this? Is there a way to make it not do this?

Answer 1

Entity Framework edmx generator uses DefiningQuery element to define entity set based on view, and it explicitly specifies view's SQL query:

<EntitySet Name="custview" EntityType="AdventureWorksLTModel.Store.custview">
  <DefiningQuery>SELECT [custview].[CustomerID] AS [CustomerID], 
   [custview].[FirstName] AS [FirstName], 
   [custview].[LastName] AS [LastName], 
   [custview].[CompanyName] AS [CompanyName]
   FROM [dbo].[custview] AS [custview]
  </DefiningQuery>
</EntitySet>

so, the only way EF can apply filtering expression to DefiningQuery is to wrap it with subquery. If you believe it's not efficient for your database, you can manually edit edmx file with in any text editor and specify any query you want.

There's also QueryView element that can possibly be more effective