Entity Framework Generated SQL for Entity Mapped to a View

Question

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:

SELECT iMovieID, vchName, dtInsertDate
FROM dbo.t_Movie WITH (NOLOCK)

The table t_Movie has the following definition:

CREATE TABLE [dbo]. %5BiMovieID%5D %5Bint%5D IDENTITY%281,1">t_Movie NOT NULL,
[vchName] varchar NOT NULL,
[dtInsertDate] [datetime] NULL,
CONSTRAINT [PK_t_Movie] PRIMARY KEY CLUSTERED
( [iMovieID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

When I write a simple Linq to Entities Query like so:

 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);
            }

Here is the SQL generated by the Entity framework captured by the profiler:

SELECT
[Extent1].[iMovieID] AS [iMovieID],
[Extent1].[vchName] AS [vchName],
[Extent1].[dtInsertDate] AS [dtInsertDate]
FROM (SELECT
[v_Movie].[iMovieID] AS [iMovieID],
[v_Movie].[vchName] AS [vchName],
[v_Movie].[dtInsertDate] AS [dtInsertDate]
FROM [dbo].[v_Movie] AS [v_Movie]) AS [Extent1]
WHERE (CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) > 0

The DBA has concern that the Inner SELECT:

SELECT
[v_Movie].[iMovieID] AS [iMovieID],
[v_Movie].[vchName] AS [vchName],
[v_Movie].[dtInsertDate] AS [dtInsertDate]
FROM [dbo].[v_Movie] AS [v_Movie]) AS [Extent1]

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.

Any particular reason why EF needs to do this, is there any reason why the following could not have been the generated SQL:

SELECT
[v_Movie].[iMovieID] AS [iMovieID],
[v_Movie].[vchName] AS [vchName],
[v_Movie].[dtInsertDate] AS [dtInsertDate]
FROM [dbo].[v_Movie] AS [v_Movie]
WHERE (CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) > 0

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:

BEGIN
declare @counter int
set @counter = 0
while @counter < 100000
begin
set @counter = @counter + 1

INSERT INTO t_Movie(vchName) values('Movie'+CONVERT(varchar,@counter))
end
END

Is this a valid concern?

P.S -

(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.

Any insights would be much appreciated

Answer 1

Look at your .EDMX file using an XML Editor. You will find something there for the movie view where it has a select statement for the view. Remove the select statement and make the rest of the view XML look more like your tables. You are getting this inner select because EF things naively that you are trying to map the columns to different names in the view rather than the default names.

Answer 2

I got this answer from MSDN forums and it makes perfect sense:

Entity Framework Generated SQL