LINQ To Entities, SQL and performance

I have introduced my photo gallery building process in couple of postings. As my galleryuses 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
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.


Leave a Reply

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