title: Entity Framework Generated SQL for Entity Mapped to a View
body: <p>I've mapped an EDM entity to a database(SQL Server 2005) View. 
  The entity is a simple Movie Entity which has properties of ID, Name and DateInserted which corresponds to a View which has the following definition:</p>
  
  <blockquote>
    <p>SELECT iMovieID, vchName, dtInsertDate<br/>
    FROM  dbo.t_Movie WITH (NOLOCK)</p>
  </blockquote>
  
  <p>The table t_Movie has the following definition:</p>
  
  <blockquote>
    <p>CREATE TABLE [dbo].[t_Movie](<br/>
        [iMovieID] [int] IDENTITY(1,1) NOT NULL,<br/>
        [vchName] varchar NOT NULL,<br/>
        [dtInsertDate] [datetime] NULL,<br/>
     CONSTRAINT [PK_t_Movie] PRIMARY KEY CLUSTERED <br/>
    (
        [iMovieID] ASC<br/>
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, <br/>ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]<br/>
    ) ON [PRIMARY]<br/>
    GO<br/></p>
  </blockquote>
  
  <p>When I write a simple Linq to Entities Query like so:</p>
  
  <pre><code> var q = from m in context.v_Movie where m.vchName.Contains("Ocean") select m;
              foreach (var movie in q)
              {
                  Console.WriteLine("{0}:{1}",movie.iMovieID, movie.vchName);
              }
  </code></pre>
  
  <p>Here is the SQL generated by the Entity framework captured by the profiler:</p>
  
  <blockquote>
    <p>SELECT <br/>
    [Extent1].[iMovieID] AS [iMovieID], <br/>
    [Extent1].[vchName] AS [vchName], <br/>
    [Extent1].[dtInsertDate] AS [dtInsertDate]<br/>
    FROM (SELECT <br/>
          [v_Movie].[iMovieID] AS [iMovieID], <br/>
          [v_Movie].[vchName] AS [vchName], <br/>
          [v_Movie].[dtInsertDate] AS [dtInsertDate]<br/>
          FROM [dbo].[v_Movie] AS [v_Movie]) AS [Extent1]<br/>
    WHERE (CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) > 0<br/></p>
  </blockquote>
  
  <p>The DBA has concern that the Inner SELECT:</p>
  
  <blockquote>
    <p>SELECT <br/>
    [v_Movie].[iMovieID] AS [iMovieID],<br/> 
    [v_Movie].[vchName] AS [vchName], <br/>
    [v_Movie].[dtInsertDate] AS [dtInsertDate]<br/>
    FROM [dbo].[v_Movie] AS [v_Movie]) AS [Extent1]<br/><br/>
    will cause some serious performance issues over time as the table grows since its selecting all the rows from the view into a temp table([Extent1]) and then the outer SELECT is selecting from this temp table.</p>
  </blockquote>
  
  <p>Any particular reason why EF needs to do this, is there any reason why the following could not have been the generated SQL:</p>
  
  <blockquote>
    <p>SELECT <br/>
          [v_Movie].[iMovieID] AS [iMovieID], <br/>
          [v_Movie].[vchName] AS [vchName],<br/> 
          [v_Movie].[dtInsertDate] AS [dtInsertDate]<br/>
          FROM [dbo].[v_Movie] AS [v_Movie]<br/>
    WHERE (CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) > 0<br/></p>
  </blockquote>
  
  <p>I populated the table with 100,000 records using the following SQL but did not notice any performance degradation when executing the LINQ query. Profiler showed that the query ran under a second:</p>
  
  <blockquote>
    <p>BEGIN<br/>
    declare @counter int<br/>
        set @counter = 0<br/>
        while @counter &lt; 100000<br/>
        begin<br/>
          set @counter = @counter + 1<br/><br>
          INSERT INTO t_Movie(vchName) values('Movie'+CONVERT(varchar,@counter)) <br/>
        end<br/>
    END<br/></p>
  </blockquote>
  
  <p>Is this a valid concern? </p>
  
  <p>P.S - </p>
  
  <blockquote>
    <p>(CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) 
    is not a concern here since the LINQ to Entities query I've used is just for illustration.</p>
  </blockquote>
  
  <p>Any insights would be much appreciated</p>