Caching with LINQ (“delayed” execution)

To increase the site performance, I cached (at least I thought I did) the result from a LINQ query into Application.Cache. The code is like this:

public static List<Region> GetAllRegions()
if (MyCache.GetCache(“RegionListFromDB”) == null)
using (MyInfoDataContext db = new MyInfoDataContext())
var regions = from r in db.Regions
select r;
regions, SiteConfig.LongCache);
return regions as List<Region>;
return MyCache.GetCache(“RegionListFromDB”) as List<Region>;

It looks good, when I debug through it I did see that the copy in the cache was returned to the caller. However the SQL Profiler told me that the database still got hit every time. It turned out that as soon as the caller accessed the returned List<>  in foreach loop, the database will be accessed. It looks like that it only cached the “command” to get the data, not the data itself.

Later I found out that by default LINQ uses “delayed” execution/loading, until the very first time the data is needed. Some methods will force immediate loading: Count(), Max()…. and ToList(), ToArray(). So this code change made my caching working:

List<Region> reList = (from r in db.Regions select r).ToList();
MyCache.AddToAbsoluteCache(“RegionListFromDB”, reList, SiteConfig.LongCache);

There is another evidence of this “delayed” execution. See this method to get total item count from a table:

var items = from i in db.ForSaleItems
where i.SiteID == SiteConfig.SiteID
select i;
items = from i in items
where  i.Region.RegionNameEN == region  && i.Category.CategoryNameEn == category
select i;
if (items2 != null)
return items.Count();
return 0;

Through SQL Profiler, I can see that the database was only accessed once, and that’s by command items.Count(), and the SQL query string is the combination of both LINQ query.  You can put if statements to build LINQ query conditionally.

Another trick, if the data is reayonly, set ObjectTrackingEnabled = false on the LINQ data context to speed up the loading.

This entry was posted in .NET, ASP.NET and tagged . Bookmark the permalink.

One Response to Caching with LINQ (“delayed” execution)

  1. Alireza says:

    Very nice advice and trick. Thank you man.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s