Over the weekend I’ve been working on a query abstraction approach which is interoperable with both LINQ to SQL and the ADO.net Entity Framework (using IQueryable) – with some minor modifications to suit each data model.
As I was putting this to use on a somewhat semi-realistic search pane, I had a rude reminder of one of the largest problems with the ADO.net Entity Framework – ugly T-SQL.
Take this fairly simple search form below. It supports pagination and sorting and (at present) multiple search terms (just artiist and/or album at this stage). For the sake of being fancy, we also render the album cover if specified. Neat, huh?
This view of the data is denormalized from normalized tables and doesn’t really select much data – a couple of joins and it’s paginated so only a handful of rows are returned. Tops, this is a small two-query operation to load the results. Should scale fine assuming the query is done right. Running a basic query with LINQ to SQL produces this in SQL Profiler:
This is close to something a decent developer would put together in a stored procedure potentially. No over-use of joins or sub queries, and this should be fairly straightforward to tune in terms of indexing etc.
Let’s take a look at the same operation with the ADO.net Entity Framework – the query is constructed identically, except it’s using an EDMX model instead of a DBML model:
That’s right – the exact same query runs off the page! Here is the full text copied from SQL Profiler:
exec sp_executesql N’SELECT TOP (10)
[Project5].[C1] AS [C1],
[Project5].[AlbumId] AS [AlbumId],
[Project5].[Name1] AS [Name],
[Project5].[Name] AS [Name1],
[Project5].[Name2] AS [Name2],
[Project5].[Release] AS [Release],
[Project5].[Catalog] AS [Catalog],
[Project5].[C2] AS [C2],
[Project5].[NumberOfRecords] AS [NumberOfRecords],
[Project5].[YearReleased] AS [YearReleased],
[Project5].[YearReReleased] AS [YearReReleased]
FROM ( SELECT [Project5].[AlbumId] AS [AlbumId], [Project5].[Name] AS [Name], [Project5].[YearReleased] AS [YearReleased], [Project5].[YearReReleased] AS [YearReReleased], [Project5].[Catalog] AS [Catalog], [Project5].[NumberOfRecords] AS [NumberOfRecords], [Project5].[Release] AS [Release], [Project5].[Name1] AS [Name1], [Project5].[Name2] AS [Name2], [Project5].[C1] AS [C1], [Project5].[C2] AS [C2], row_number() OVER (ORDER BY [Project5].[Release] ASC) AS [row_number]
FROM ( SELECT
[Project4].[AlbumId] AS [AlbumId],
[Project4].[Name] AS [Name],
[Project4].[YearReleased] AS [YearReleased],
[Project4].[YearReReleased] AS [YearReReleased],
[Project4].[Catalog] AS [Catalog],
[Project4].[NumberOfRecords] AS [NumberOfRecords],
[Project4].[Release] AS [Release],
[Project4].[Name1] AS [Name1],
[Project4].[Name2] AS [Name2],
1 AS [C1],
CASE WHEN ([Project4].[C1] IS NULL) THEN @p__linq__2 ELSE [Project4].[C2] END AS [C2]
FROM ( SELECT
[Project2].[AlbumId] AS [AlbumId],
[Project2].[Name] AS [Name],
[Project2].[YearReleased] AS [YearReleased],
[Project2].[YearReReleased] AS [YearReReleased],
[Project2].[Catalog] AS [Catalog],
[Project2].[NumberOfRecords] AS [NumberOfRecords],
[Project2].[Release] AS [Release],
[Project2].[Name1] AS [Name1],
[Project2].[Name2] AS [Name2],
[Project2].[C1] AS [C1],
(SELECT TOP (1)
[Extent5].[FileName] AS [FileName]
FROM [dbo].[Media] AS [Extent5]
WHERE [Project2].[AlbumId] = [Extent5].[AlbumId]) AS [C2]
FROM ( SELECT
[Extent1].[AlbumId] AS [AlbumId],
[Extent1].[Name] AS [Name],
[Extent1].[YearReleased] AS [YearReleased],
[Extent1].[YearReReleased] AS [YearReReleased],
[Extent1].[Catalog] AS [Catalog],
[Extent1].[NumberOfRecords] AS [NumberOfRecords],
[Extent1].[Release] AS [Release],
[Extent2].[Name] AS [Name1],
[Extent3].[Name] AS [Name2],
(SELECT TOP (1)
[Extent4].[MediaId] AS [MediaId]
FROM [dbo].[Media] AS [Extent4]
WHERE [Extent1].[AlbumId] = [Extent4].[AlbumId]) AS [C1]
FROM [dbo].[Album] AS [Extent1]
LEFT OUTER JOIN [dbo].[Artist] AS [Extent2] ON [Extent1].[ArtistId] = [Extent2].[ArtistId]
LEFT OUTER JOIN [dbo].[Country] AS [Extent3] ON [Extent1].[CountryId] = [Extent3].[CountryId]
) AS [Project2]
) AS [Project4]
) AS [Project5]
) AS [Project5]
WHERE [Project5].[row_number] > 0
ORDER BY [Project5].[Release] ASC’,N’@p__linq__2 nvarchar(4000)’,@p__linq__2=N”
Wouldn’t you agree that there is a significant difference between the two frameworks? I just don’t think you could risk taking the Entity Framework into a high volume system architecture the way it stands now. Is anyone out there suffering the Entity Framework with this garbage SQL?
We can only hope that this is the number one improvement in Entity Framework v.Next. In coming blog entries I will
b walking you through the approach I’ve taken to create a query design approach.
If you are having trouble with either LINQ to SQL or the Entity Framework don’t hesitate to drop me an email rob.sanders@gmail.com with the problem in the Subject header (to avoid my skipping the email).
