LINQ To Entities, SQL and performance
I have introduced my photo gallery building process in couple of postings. As my gallery uses Entity Framework 4.0 it is good source for some internal processing that takes place inside Entity Framework. In this posting I will show you some LINQ To Entities queries and SQL that is generated for them. I added also some thoughts about performance.
My gallery model is shown here with database structure.
 |  |
| Class diagram of gallery. | Database structure of gallery. |
Get all public gallery items that have no parent album
First example shows LINQ query that returns all gallery items that have no parent album. I can use this query for gallery main page by example.
query = from g in context.GalleryItems
where g.ParentAlbum == null &&
g.Visible
select g;
This query produces the following SQL query. Take a closer look at it and see how inheritance hierarchy is handled here.
SELECT
1 AS [C1],
CASE
WHEN ([UnionAll1].[C1] = 1)
THEN '0X0X'
ELSE '0X1X'
END AS [C2],
[UnionAll1].[gallery_item_id] AS [C3],
[Extent3].[title] AS [title],
[Extent3].[description] AS [description],
[Extent3].[visible] AS [visible],
CASE
WHEN ([UnionAll1].[C1] = 1)
THEN CAST(NULL AS varchar(1))
ELSE [UnionAll1].[file_name]
END AS [C4],
CASE
WHEN ([UnionAll1].[C1] = 1)
THEN CAST(NULL AS bit)
ELSE [UnionAll1].[gallery_thumb]
END AS [C5],
[Extent3].[parent_album_id] AS [parent_album_id]
FROM (
SELECT
[Extent1].[gallery_item_id] AS [gallery_item_id],
[Extent1].[file_name] AS [file_name],
[Extent1].[gallery_thumb] AS [gallery_thumb],
cast(0 as bit) AS [C1]
FROM
[dbo].[photo] AS [Extent1]
UNION ALL
SELECT
[Extent2].[gallery_item_id] AS [gallery_item_id],
CAST(NULL AS varchar(1)) AS [C1],
CAST(NULL AS bit) AS [C2],
cast(1 as bit) AS [C3]
FROM
[dbo].[album] AS [Extent2]
) AS [UnionAll1]
INNER JOIN
[dbo].[gallery_item] AS [Extent3] ON
[UnionAll1].[gallery_item_id] = [Extent3].[gallery_item_id]
WHERE
([Extent3].[visible] = 1) AND
([Extent3].[parent_album_id] IS NULL)
Get all top level galleries
Well… I have one simple rule in my gallery – all gallery items that are not albums must have parent album. Query shown above returns always only Album type objects. Let’s say now in LINQ To Entities query that we need only albums to be searched.
query = from g in context.GalleryItems.OfType<Album>()
where g.ParentAlbum == null &&
g.Visible
select g;
This is the query that is produced by Entity Framework.
SELECT
1 AS [C1],
'0X0X' AS [C2],
[Extent1].[gallery_item_id] AS [gallery_item_id],
[Extent2].[title] AS [title],
[Extent2].[description] AS [description],
[Extent2].[visible] AS [visible],
[Extent2].[parent_album_id] AS [parent_album_id]
FROM
[dbo].[album] AS [Extent1]
INNER JOIN [dbo].[gallery_item] AS [Extent2]
ON [Extent1].[gallery_item_id] = [Extent2].[gallery_item_id]
WHERE
([Extent2].[visible] = 1) AND
([Extent2].[parent_album_id] IS NULL)
We can see that this query is not so complex than previous one. It is shorter and doesn’t have any complex parts like unions and from clauses that contain sub-query.
Getting better performance
Two examples above showed us very well how same thing can be done in more that one way. First example produced long and complex SQL-query while second example produced short and simple query. Both of them return exactly the same results.
When writing LINQ To Entities queries it is very good idea to make sure that queries use minimal set of data. If we have 1.000 albums and 100.000 photos then second query takes less time to execute because it contains no selects or joins to photos table.
|
|
|
|
|