A Sneak Peek at the Entity Framework v4 Beta 1


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

image

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)!


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>