This article explains an approach to lazy load a database query on an ASP.net MVC controller to prevent the query from executing on it, and just execute on the View, if it's not cached. So, if the View is cached, the database is not queried.
With ASP.net MVC you would obviously have a Model, a View and a Controller. But the workflow of a page execution on MVC is not really M-V-C, because it doesn't happens on this order. Skipping a few steps, to keep the explanation simpler, let's start with the Controller. On the controller it's common to query a database on the Model and set the View model with the data. On the view, you usually have a Html helper that will take the view model and render it as Html.
There lies a problem. If you use caching on your HTML helper, the database is still queried, because the query would be issued on the Controller, which happens before the View (which is cached).
To understand this, I recommend you to take a look on a previous article: http://tsayao.blogspot.com/2009/07/caching-html-helpers-on-aspnet-mvc.html.
The solution is pretty simple: Lazy load the data on the Controller.
This way, the database is not queried on the Controller, it's just lazy loaded when the Enumeration is requested at the View. So, if the Html helper on the View is cached, it's never requested!
First, let's implement a simple LazyColletion class:
using System.Collections;
using System.Collections.Generic;
namespace DotNetBurner
{
public class LazyCollection<t> : IEnumerable<t>
{
public delegate IEnumerable<t> LazyCallback();
private readonly LazyCallback _callBack;
private IEnumerable<t> _items;
private bool _wasCalled = false;
public LazyCollection(LazyCallback callBack)
{
_callBack = callBack;
}
IEnumerator IEnumerable.GetEnumerator()
{
CallCallBack();
return _items.GetEnumerator();
}
public IEnumerator<t> GetEnumerator()
{
CallCallBack();
return _items.GetEnumerator();
}
void CallCallBack()
{
lock (this)
{
if (!_wasCalled)
_items = _callBack();
_wasCalled = true;
}
}
}
}
The limitation of this example is that your database query method should return a collection that implements IEnumerable<T>. Something like this:
public virtual IEnumerable<Tag> GetTags(int top)
{
List<Tag> tags = new List<Tag>();
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand("sp_tag_get_tops", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("top", top);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Tag tag = new Tag
{
StoryCount = reader.GetInt32("Count"),
TagId = reader.GetInt32("TagId"),
Name = reader.GetString("Name")
};
tags.Add(tag);
}
}
}
}
return tags;
}
On the Controller, you can do something like this:
viewData.Tags = new LazyCollection<Tag>(() => Repository.GetTags(StorySettings.Settings.TopTags));
This way, the database is just queried when you use the View Model. On this case, if you never enumerate ViewData.Model.Tags (it happens when you cache the view part that touches it), no query is issued on the database. This example uses the SqlClient directly, but i think you can extend it to use Linq to Entities.