Hi All,
This is just a quick post to advertise some of the high hopes I have for the upcoming Entity Framework v4 which will ship with Visual Studio 2010. The next version is bumped from v2 to v4 (in order to align with the version of the .Net Framework) and will contain a lot of improvements over the initial release (which shipped with Visual Studio 2008 Service Pack 1, last year).
There are a number of improvements, however I was just keen to see a rewrite of the SQL provider, as the quality of the generated queries has left much to be desired. Just this evening, I fired off a query under the SQL Profiler to see what kind of T-SQL we might expect from the Entity Framework v4 (in Beta).
I’m using the old Movie Database schema which maps a main “Movie” table to “Actor” and Director” via join tables (“MovieActor” and “MovieDirector”). To see how well EF v4 handles even the most horrible of queries, I put together the following hideous query:
var movies = (from m in db.Movies
from ma in db.MovieActors
from a in db.Actors
from md in db.MovieDirectors
from d in db.Directors
where ma.Actor == a
where ma.Movie == m
where a.Name.Contains("James")
where md.Movie == m
where md.Director == d
where d.Name.Contains("A")
select m);
Which, when executed resulted in the following T-SQL:
SELECT
[Filter1].[MovieId1] AS [MovieId],
[Filter1].[MovieTitle] AS [MovieTitle],
…
[Filter1].[DTS] AS [DTS]
FROM (SELECT [Extent1].[MovieId] AS [MovieId1], [Extent1].[MovieTitle] AS [MovieTitle], [Extent1].[Overview] AS [Overview], [Extent1].[Rating] AS [Rating], [Extent1].[Info] AS [Info], [Extent1].[Region] AS [Region], [Extent1].[NumDiscs] AS [NumDiscs], [Extent1].[YearReleased] AS [YearReleased], [Extent1].[Writing] AS [Writing], [Extent1].[Length] AS [Length], [Extent1].[Case] AS [Case], [Extent1].[CaseIndex] AS [CaseIndex], [Extent1].[NewRelease] AS [NewRelease], [Extent1].[ComingSoon] AS [ComingSoon], [Extent1].[TopTen] AS [TopTen], [Extent1].[IsMovie] AS [IsMovie], [Extent1].[BoxSet] AS [BoxSet], [Extent1].[Borrowed] AS [Borrowed], [Extent1].[OutOfPrint] AS [OutOfPrint], [Extent1].[CriterionCollection] AS [CriterionCollection], [Extent1].[ExRental] AS [ExRental], [Extent1].[Sealed] AS [Sealed], [Extent1].[Watched] AS [Watched], [Extent1].[Superbit] AS [Superbit], [Extent1].[DTS] AS [DTS], [Extent4].[DirectorId] AS [DirectorId]
FROM [dbo].[Movie] AS [Extent1]
INNER JOIN [dbo].[MovieActor] AS [Extent2] ON [Extent2].[MovieId] = [Extent1].[MovieId]
INNER JOIN [dbo].[Actor] AS [Extent3] ON [Extent2].[ActorId] = [Extent3].[ActorId]
INNER JOIN [dbo].[MovieDirector] AS [Extent4] ON [Extent4].[MovieId] = [Extent1].[MovieId]
WHERE ( CAST(CHARINDEX(N’James’, [Extent3].[Name]) AS int)) > 0 ) AS [Filter1]
INNER JOIN [dbo].[Director] AS [Extent5] ON [Filter1].[DirectorId] = [Extent5].[DirectorId]
WHERE ( CAST(CHARINDEX(N’A’, [Extent5].[Name]) AS int)) > 0
I think we can all agree, this is far, far better than what we would have expected from the first release of the Entity Framework which, when running the exact same query against the exact same database schema produces this T-SQL query:
SELECT
[Filter4].[MovieId1] AS [MovieId],
[Filter4].[MovieTitle] AS [MovieTitle],
…
[Filter4].[DTS] AS [DTS]
FROM ( SELECT [Filter3].[MovieId1], [Filter3].[MovieTitle], [Filter3].[Overview], [Filter3].[Rating], [Filter3].[Info], [Filter3].[Region], [Filter3].[NumDiscs], [Filter3].[YearReleased], [Filter3].[Writing], [Filter3].[Length], [Filter3].[Case], [Filter3].[CaseIndex], [Filter3].[NewRelease], [Filter3].[ComingSoon], [Filter3].[TopTen], [Filter3].[IsMovie], [Filter3].[BoxSet], [Filter3].[Borrowed], [Filter3].[OutOfPrint], [Filter3].[CriterionCollection], [Filter3].[ExRental], [Filter3].[Sealed], [Filter3].[Watched], [Filter3].[Superbit], [Filter3].[DTS], [Filter3].[MovieActorId], [Filter3].[MovieId2], [Filter3].[ActorId1], [Filter3].[ActorId2], [Filter3].[Name1], [Filter3].[Aliases], [Filter3].[MovieDirectorId], [Filter3].[DirectorId1], [Filter3].[MovieId3], [Filter3].[DirectorId2], [Filter3].[Name2]
FROM ( SELECT [Filter2].[MovieId1], [Filter2].[MovieTitle], [Filter2].[Overview], [Filter2].[Rating], [Filter2].[Info], [Filter2].[Region], [Filter2].[NumDiscs], [Filter2].[YearReleased], [Filter2].[Writing], [Filter2].[Length], [Filter2].[Case], [Filter2].[CaseIndex], [Filter2].[NewRelease], [Filter2].[ComingSoon], [Filter2].[TopTen], [Filter2].[IsMovie], [Filter2].[BoxSet], [Filter2].[Borrowed], [Filter2].[OutOfPrint], [Filter2].[CriterionCollection], [Filter2].[ExRental], [Filter2].[Sealed], [Filter2].[Watched], [Filter2].[Superbit], [Filter2].[DTS], [Filter2].[MovieActorId], [Filter2].[MovieId2], [Filter2].[ActorId1], [Filter2].[ActorId2], [Filter2].[Name1], [Filter2].[Aliases], [Filter2].[MovieDirectorId], [Filter2].[DirectorId1], [Filter2].[MovieId3], [Filter2].[DirectorId2], [Filter2].[Name2]
FROM ( SELECT [Filter1].[MovieId1], [Filter1].[MovieTitle], [Filter1].[Overview], [Filter1].[Rating], [Filter1].[Info], [Filter1].[Region], [Filter1].[NumDiscs], [Filter1].[YearReleased], [Filter1].[Writing], [Filter1].[Length], [Filter1].[Case], [Filter1].[CaseIndex], [Filter1].[NewRelease], [Filter1].[ComingSoon], [Filter1].[TopTen], [Filter1].[IsMovie], [Filter1].[BoxSet], [Filter1].[Borrowed], [Filter1].[OutOfPrint], [Filter1].[CriterionCollection], [Filter1].[ExRental], [Filter1].[Sealed], [Filter1].[Watched], [Filter1].[Superbit], [Filter1].[DTS], [Filter1].[MovieActorId], [Filter1].[MovieId2], [Filter1].[ActorId1], [Filter1].[ActorId2], [Filter1].[Name1], [Filter1].[Aliases], [Filter1].[MovieDirectorId], [Filter1].[DirectorId1], [Filter1].[MovieId3], [Filter1].[DirectorId2], [Filter1].[Name2]
FROM ( SELECT [Extent1].[MovieId] AS [MovieId1], [Extent1].[MovieTitle] AS [MovieTitle], [Extent1].[Overview] AS [Overview], [Extent1].[Rating] AS [Rating], [Extent1].[Info] AS [Info], [Extent1].[Region] AS [Region], [Extent1].[NumDiscs] AS [NumDiscs], [Extent1].[YearReleased] AS [YearReleased], [Extent1].[Writing] AS [Writing], [Extent1].[Length] AS [Length], [Extent1].[Case] AS [Case], [Extent1].[CaseIndex] AS [CaseIndex], [Extent1].[NewRelease] AS [NewRelease], [Extent1].[ComingSoon] AS [ComingSoon], [Extent1].[TopTen] AS [TopTen], [Extent1].[IsMovie] AS [IsMovie], [Extent1].[BoxSet] AS [BoxSet], [Extent1].[Borrowed] AS [Borrowed], [Extent1].[OutOfPrint] AS [OutOfPrint], [Extent1].[CriterionCollection] AS [CriterionCollection], [Extent1].[ExRental] AS [ExRental], [Extent1].[Sealed] AS [Sealed], [Extent1].[Watched] AS [Watched], [Extent1].[Superbit] AS [Superbit], [Extent1].[DTS] AS [DTS], [Extent2].[MovieActorId] AS [MovieActorId], [Extent2].[MovieId] AS [MovieId2], [Extent2].[ActorId] AS [ActorId1], [Ex
tent3].[ActorId] AS [ActorId2], [Extent3].[Name] AS [Name1], [Extent3].[Aliases] AS [Aliases], [Extent4].[MovieDirectorId] AS [MovieDirectorId], [Extent4].[DirectorId] AS [DirectorId1], [Extent4].[MovieId] AS [MovieId3], [Extent5].[DirectorId] AS [DirectorId2], [Extent5].[Name] AS [Name2]
FROM [dbo].[Movie] AS [Extent1]
CROSS JOIN [dbo].[MovieActor] AS [Extent2]
CROSS JOIN [dbo].[Actor] AS [Extent3]
CROSS JOIN [dbo].[MovieDirector] AS [Extent4]
CROSS JOIN [dbo].[Director] AS [Extent5]
WHERE [Extent2].[ActorId] = [Extent3].[ActorId]
) AS [Filter1]
WHERE [Filter1].[MovieId2] = [Filter1].[MovieId1]
) AS [Filter2]
WHERE [Filter2].[MovieId3] = [Filter2].[MovieId1]
) AS [Filter3]
WHERE [Filter3].[DirectorId1] = [Filter3].[DirectorId2]
) AS [Filter4]
WHERE ((CAST(CHARINDEX(N’James’, [Filter4].[Name1]) AS int)) > 0) AND ((CAST(CHARINDEX(N’A’, [Filter4].[Name2]) AS int)) > 0)
Stay tuned for more Entity Framework v4 news and info as I start to play with some of the new functionality including the support for Plain Old CLR objects (POCO)!
