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
MyCache.AddToAbsoluteCache(“RegionListFromDB”, 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
items = from i in items
where i.Region.RegionNameEN == region && i.Category.CategoryNameEn == category
if (items2 != null)
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.