Linq To SQL Caching Adventures Part 1

A caching solution is always necessary if you want to build scalable applications which will handle lots of users and heavy data access without requiring more hardware resources. I’ve already talked about caching when using Entity Framework, but is there a way to use caching in Linq to SQL and what are the problems one might face.

To explore caching in Linq to SQL I’ve built a small web site that had a single page. This page used Linq To SQL to access the AdventureWorks sample Database which you can download from Codeplex.

The very simple Linq To SQL model that I’ve created had three entities, a Peson and its Phones and Email addresses.

 

The site default page contained a repeater that was databound to the first Person that his last name begun with the letter M, fetched by a call to the GetPerson method.

public partial class _Default : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      DataBind();
    }
  }

  public override void DataBind()
  {
    base.DataBind();

    employeesRepeater.DataSource = new List<Person>() { GetPerson("M") };
    employeesRepeater.DataBind();

  }

  protected Person GetPerson(string lastNameStartsWith)
  {
    using (AdventureworksDataContext context = new AdventureworksDataContext())
    {
      return context.Persons.Where(p => p.LastName.StartsWith(lastNameStartsWith)).FirstOrDefault();
    }
  }
}

This works, but imagine tens of thousand of users accessing this page at the same time asking for the same thing. This would result in thousand of SQL commands reaching SQL server. And in this case the SQL command is trivial but what would happen if the query was more complex? The answer is that this page would become significantly slower as more users would try to access it.

To overcome this, I’ve introduced a simple Caching mechanism to the page utilizing the System.Web.Caching.Cache Object of the Page and changed the GetPerson method to use this caching mechanism.

protected Person GetPerson(string lastNameStartsWith)
{
  using (AdventureworksDataContext context = new AdventureworksDataContext())
  {
    return CacheGet<Person>(
      () => context.Persons.Where(p => p.LastName.StartsWith(lastNameStartsWith)).FirstOrDefault(),
      string.Format("Person_LastName_StartsWith:{0}", lastNameStartsWith));
  }
}

protected T CacheGet<T>(Func<T> loader, string cacheKey) where T : class
{
  var cachedObject = this.Cache[cacheKey] as T;
  if (cachedObject == null)
  {
    cachedObject = loader.Invoke();
  }
  return cachedObject;
}

	

 

Now only the first call to the GetPerson method results into a query being made to the SQL Server, all resulting calls will be served by the Cache (that is of course if the cache key aka last name character matches) and its going to be equally fast for either one user or million of users. So what’s the problem? Anyone care to guess?

To be continued…