我已将EDM实体映射到数据库(SQL Server 2005)View . 该实体是一个简单的Movie Entity,它具有ID,Name和DateInserted属性,它们对应于具有以下定义的View:
SELECT iMovieID,vchName,dtInsertDate FROM dbo.t_Movie WITH(NOLOCK)
表t_Movie具有以下定义:
CREATE TABLE [dbo] . [t_Movie]([iMovieID] [int] IDENTITY(1,1)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
当我像这样写一个简单的Linq to Entities Query时:
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);
}
以下是由探查器捕获的实体框架生成的SQL:
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
DBA关注内部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]将导致一些严重的性能问题,因为它从视图中选择所有行到临时表([Extent1]),然后外部SELECT从此临时表中选择 .
EF需要执行此操作的任何特定原因,是否有任何原因导致以下内容无法生成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
我使用以下SQL在表中填充了100,000条记录,但在执行LINQ查询时没有注意到任何性能下降 . Profiler显示该查询在一秒钟内运行:
BEGIN声明@counter int set @counter = 0而@counter <100000开始设置@counter = @counter 1 INSERT INTO t_Movie(vchName)值('Movie'CONVERT(varchar,@ counter))结束END
这是一个有效的问题吗?
P.S -
(CAST(CHARINDEX(N'Ocean',[Extent1] . [vchName])AS int))这里不是问题,因为我使用的LINQ to Entities查询仅用于说明 .
任何见解都将非常感激
2 回答
使用XML编辑器查看.EDMX文件 . 您将在电影视图中找到一些内容,其中包含视图的select语句 . 删除select语句,使视图的其余部分看起来更像表格 . 你正在获得这个内部选择,因为EF天真地试图将列映射到视图中的不同名称而不是默认名称 .
我从MSDN论坛得到了这个答案,这很有道理:
Entity Framework Generated SQL