Geeks With Blogs
Old blog. Blog moved to thomasgathings.com I would delete this...but I don't see the option

I was working on a small web application that was built very quickly using Linq to SQL and I wanted to ensure that a particular page wasn't loading more data than needed into memory. This particular page had a simple GridView, and in the one and only item template, there was an ASP.Net Image (along with some other stuff).

The data source was of type IQueryable<Article> and an Article may have zero to many ArticleImages. A relationship was present in the dbml, so the ArticleImages were generated as an entity set property. The requirements were to show a thumbnail if there was an image (the first) or else nothing.

On the image, I set ImageUrl to

<%# DataBinder.Eval(Container.DataItem,"ArticleId","~/GetArticleImage.ashx?thumbnail=y&articleid={0}" ) %>

and Visible to the expresssion:

<%# ((Article)Container.DataItem).ArticleImages.Count %>

 The page rendered properly but when I checked the data context log, I noticed that for every row in the GridView, the entire ArticleImage row was being loaded. As it turns out, this is well-documented behavior; in an object graph, if any property of an entity set is accessed, the entire entity is loaded. It is lazy-loaded by default and while this can be changed with DataLoadOptions, I don't believe you can specify options for individual properties.

What I needed at this point was an efficient way to test whether or not there were any images. There are other ways to do this, such as:

  • add a calculated column leveraging a scalar UDF on your table
  • work with a view exposing only narrow metadata
  • separate your metadata into an intermediary table

 I went with the addition of a property on the Article entity that counts a projected int and it looks like this:

public bool HasImage

{

    get

    {

        GreenCreditDataContext db = DataContextSessionManager<GreenCreditDataContext>.Instance;

        return db.ArticleImages.Where(x => x.ArticleId == ArticleId).Count() > 0;

 

    }

}

Ok so that problem was solved. However, there was another one. In the handler code that streams the image data to the browser, I was also loading the entire ArticleImage row. This was not necessary as I only needed the title and either the thumbnail data or the image data. Originally, I was calling the FirstOrDefault extension method on IQueryable<ArticleImage> from the data context. After a null check, I accessed either the ThumbnailData or the ImageData property, depending on the existence of a simple thumbnail querystring. However, the log shows that the entire row was loaded. My solution to this was to exercise Where (with the same Func expression used earlier), then chain that to a projection.

if(thumbnail)
{
    var s = articleImages
        .Select(x => new { Data = x.ThumbnailData, Title = x.Title })
        .FirstOrDefault();
 
    if (null != s) Write(s.Data, s.Title);
}
else
{
    var s = articleImages
        .Select(x => new { Data = x.ImageData, Title = x.Title})
        .FirstOrDefault();
 
    if (null != s) Write(s.Data, s.Title);
}
To wrap up, especially when working with datatypes of unrestrained size (such as varbinary(max)), check the logs to ensure your optimizing as necessary.

Posted on Monday, December 1, 2008 7:56 AM | Back to top


Comments on this post: Linq to SQL: an optimization on working with object graphs

# re: Linq to SQL: an optimization on working with object graphs
Requesting Gravatar...
Thanks for your comments
Left by Barbaros Tombaz on Oct 30, 2009 4:45 PM

Your comment:
 (will show your gravatar)


Copyright © Thomas Gathings | Powered by: GeeksWithBlogs.net