Caching LinqToSql results with SqlCacheDependency

What´s this?

This page and any other page I may publish on this site contains some code that I´ve written in my projects.
I´ll use these pages as a repository for reusable code, but it´s always fun if someone else can use (or comment, good or bad) the code.

Caching Linq results using SqlCacheDependency

In early 2008 I needed to cache the results from LinqToSql-queries. I like the SqlCacheDependency (especially in SQL 2K5 Server).
I also like the new extension methods posibillity... So this is what I came up with.

The code should be explained enough by the comments I think.

    public static class Cache
    {
        /// <summary>
        /// Caches Linq query that is created for LinqToSql.
        /// Limits are the same as SqlCacheDependency
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="q">The linq query</param>
        /// <param name="dc">Your LinqToSql DataContext</param>
        /// <param name="CacheId">The unique Id for the cache</param>
        /// <returns></returns>
        public static List<T> LinqCache<T>(this System.Linq.IQueryable<T> q, System.Data.Linq.DataContext dc, string CacheId)
        {
            try
            {
                List<T> objCache = (List<T>)System.Web.HttpRuntime.Cache.Get(CacheId);
 
                if (objCache == null)
                {
                    /////////No cache... implement new SqlCacheDependeny//////////
                    //1. Get connstring from DataContext
                    string connStr = dc.Connection.ConnectionString;
                    //2. Get SqlCommand from DataContext and the LinqQuery
                    string sqlCmd = dc.GetCommand(q).CommandText;                    
                    //3. Create Conn to use in SqlCacheDependency
                    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
                    {
                        conn.Open();
                        //4. Create Command to use in SqlCacheDependency
                        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlCmd, conn))
                        {
                            //5.0 Add all parameters provided by the Linq Query
                            foreach (System.Data.Common.DbParameter dbp in dc.GetCommand(q).Parameters)
                            {
                                cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(dbp.ParameterName, dbp.Value));                                
                            }
                            //5.1 Enable DB for Notifications... Only needed once per DB...
                            System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
                            //5.2 Get ElementType for the query
                            string NotificationTable = q.ElementType.Name;
                            //5.3 Enable the elementtype for notification (if not done!)
                            if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))
                                System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);
                            //6. Create SqlCacheDependency
                            System.Web.Caching.SqlCacheDependency sqldep = new System.Web.Caching.SqlCacheDependency(cmd);
                            //7. Refresh the LinqQuery from DB so that we will not use the current Linq cache
                            dc.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, q);
                            //8. Execute SqlCacheDepency query...
                            cmd.ExecuteNonQuery();
                            //9. Execute LINQ-query to have something to cache...
                            objCache = q.ToList();
                            //10. Cache the result but use the already created objectCache. Or else the Linq-query will be executed once more...
                            System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqldep);
                        }
                    }
                }
                //Return the created (or cached) List
                return objCache;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

IMPORTANT

You need to call SqlDependency.Start for example in global.asax

   //In Application Start Event
   System.Data.SqlClient.SqlDependency.Start("DataContextConnectionString");
   //In Application End Event
   System.Data.SqlClient.SqlDependency.Stop("DataContextConnectionString");

HowTo: Call the Extension Method

So now yo can get call LinqCache<T> on any Linq.IQueryable<T>...
Check the image below for an example from my project.
203430687.png
You can also set some breakpoints in the Extension Method to really see that we collect from the cache until the DB is changed in a way that invalidate our LinqQuery.

Happy Coding

UTB