Linq to SQL Deferred Loading – Lazy Load

6 באוקטובר 2007

Linq to SQL Deferred Loading – Lazy Load

Consider the Blog Site database I used in previous posts about Linq to SQL:

Linq to SQL Deferred Loading Lazy Load

Linq to SQL Deferred Loading

If we query for the available blogs in the site using Linq to SQL, the query should look like this:

BlogDataContext ctx = new BlogDataContext(…);

 

var query = from b in ctx.Blogs

            select b;

 

foreach (Blog b in query)

{

    Console.WriteLine("{0}", b.BlogName);

}

This Linq query will result in the following SQL statement being sent to the DB:

SELECT [t0].[BlogID], [t0].[BlogName], [t0].[Owner]
FROM    [dbo].[Blogs] AS [t0]

and the output will be:

Linq to SQL Lazy Load

Now, if we change the statement that outputs the name of the blog, and let it also print the number of posts in the blog:

foreach (Blog b in query)

{

    Console.WriteLine("{0} has {1} posts", b.BlogName, b.Posts.Count);

}

This time, the output will be:

Linq to SQL Deferred Loading 

What Linq to SQL does is whenever it runs into the expression b.Posts.Count, it knows that the Posts collection of the blog was not yet retrieved, so it automatically generates the appropriate select statement and gets the posts. For example:

SELECT [t0].[PostID], [t0].[BlogID], [t0].[Title], [t0].[Body], [t0].[PublishDate]
FROM    [dbo].[Posts] AS [t0]
WHERE  [t0].[BlogID] = 'bursteg'

The problem is that a similar query is executed for each blog instance. So if we have a long list of blogs, than this simple loop can be a performance hit.

This behavior is called Linq to SQL Deferred Loading, and it is turned on by default. In order to turn it of, you can set the DeferredLoadingEnabled property to false.

BlogDataContext ctx = new BlogDataContext(…);

 

ctx.DeferredLoadingEnabled = false;

 

var query = from b in ctx.Blogs

            select b;

 

foreach (Blog b in query)

{

    Console.WriteLine("{0} has {1} posts", b.BlogName, b.Posts.Count);

}

Now, this query returns 0 as the number of posts of each blog.

DeferredLoadingEnabled

Using DataLoadOptions to Control Deferred Loading

If you still want to query the posts list for every blog that is queried, no matter if it is queries by primary key or as a result of a query, you can use DataLoadOptions.

BlogDataContext ctx = new BlogDataContext(…);

ctx.DeferredLoadingEnabled = false;

 

DataLoadOptions dlo = new DataLoadOptions();

dlo.LoadWith<Blog>(b => b.Posts);

ctx.LoadOptions = dlo;

 

var query = from b in ctx.Blogs

            select b;

 

foreach (Blog b in query)

{

    Console.WriteLine("{0} has {1} posts", b.BlogName, b.Posts.Count);

}

Using DataLoadOptions, Linq to SQL will execute only a single SQL Statement to the database, getting both the list of blogs, and the list of posts for each blog:

SELECT t0.BlogID, t0.BlogName, t0.Owner,
           t1.PostID, t1.BlogID AS BlogID2, t1.Title, t1.Body, t1.PublishDate,
           ( SELECT COUNT(*) FROM Posts AS t2
             WHERE t2.BlogID = t0.BlogID ) AS count
FROM   Blogs AS t0
LEFT OUTER JOIN Posts AS t1 ON t1.BlogID = t0.BlogID
ORDER BY t0.BlogID, t1.PostID

Enjoy!

Add comment
facebook linkedin twitter email

3 comments

  1. Bohdan12 במרץ 2008 ב 15:53

    Hi
    I spend a lot of time to find solution of problem.
    This problem relativ with Delay Loaded option

    Take a look. May be you have any ideas
    http://www.telerik.com/community/forums/thread/b311D-bbegdh.aspx

  2. Mike Nelson22 בינואר 2009 ב 2:38

    Thanks for figuring that out. This is quite often what you need to do. However, i really think it is simpler to just write the sql statement, and then you can be sure what it is doing when.

  3. hamid12 במאי 2009 ב 11:58

    Thank you very much for your helpfull Subject

Comments are closed.