CTICoder

A random spillage of programming (and other) thoughts

Linq2Sql Caching with SqlDependency

Posted by Shaun McDonnell on November 25, 2009

It took me a while to figure this one out.  I almost thought it was impossible to implement it in a ‘good way’.  However, extension methods saved the day.  So, if you want to use Sql Service Broker dependencies in your Linq2Sql code, you’ll need an extension method like this:

   1: public static List<T> LinqCache<T>(this IQueryable<T> q, DataContext dc, string key)
   2: {
   3:     SqlDependency.Start(dc.Connection.ConnectionString);
   4:     var cachedItem = (List<T>)HttpContext.Current.Cache.Get(key);
   5:  
   6:     if (cachedItem == null)
   7:     {
   8:         TracingHelper.Write("No cache key [{0}] found for query: {1}", key, dc.GetCommand(q).CommandText);
   9:         string connectionString = dc.Connection.ConnectionString;
  10:         string command = dc.GetCommand(q).CommandText;
  11:         using (var sql = new SqlConnection(connectionString))
  12:         {
  13:             sql.Open();
  14:             using (var cmd = new SqlCommand(command, sql))
  15:             {
  16:                 foreach (DbParameter dbp in dc.GetCommand(q).Parameters)
  17:                 {
  18:                     cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value));
  19:                 }
  20:                 SqlCacheDependencyAdmin.EnableNotifications(connectionString);
  21:                 string notificationTable = q.ElementType.Name;
  22:                 if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString).Contains(notificationTable))
  23:                     SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, notificationTable);
  24:                 var sqldep = new SqlCacheDependency(cmd);
  25:                 
  26:                 cmd.ExecuteNonQuery();
  27:                 cachedItem = q.ToList();
  28:                 HttpContext.Current.Cache.Insert(key, cachedItem, sqldep);
  29:             }
  30:         }
  31:     }
  32:     else
  33:     {
  34:         TracingHelper.Write("Cache key [{0}] FOUND for query:  {1}", key, dc.GetCommand(q).CommandText);
  35:     }
  36:  
  37:     SqlDependency.Stop(dc.Connection.ConnectionString);
  38:     return cachedItem;
  39: }

Now, in order to use this extension method in your queries, you’ll need to do something like this:

   1: public List<Company> Get()
   2: {
   3:     using (var dataContext = new RoutePointRepositoryDataContext(Db.ConnectionString))
   4:     {
   5:         var items = dataContext.Companies.LinqCache(dataContext, "Companies.All");
   6:         return items;
   7:     }
   8: }

The downside is that I haven’t figured out a good way to implement this when retrieving one record from the database.  So, you’ll have to do this:

   1: public Company Get(int id)
   2: {
   3:     using (var dataContext = new RoutePointRepositoryDataContext(Db.ConnectionString))
   4:     {
   5:         var company = dataContext.Companies.Where(c => c.Id == id).LinqCache(dataContext, "Company.Get." + id.ToString()).Single();
   6:         return company;
   7:     }
   8: }

 

MSDN did have some code similar to this but it did not work and there were no examples.  This works and and the examples should be enough to get you started.

-Shaun

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

 
%d bloggers like this: