Caching LinqToSql results with SqlCacheDependency

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.

SqlCacheDependency
With SqlServer 2005 SqlCacheDependency became very useful. In Sql 2000 the dependency could only be created on full tables (not rows). The Cache was destroyed as a table being cached changed even if your query did not affect the row changed, but Sql 2005 can handle SqlCacheDependency on row-level... Hence SqlCacheDependency is destroyed only if a row that my query affects is being changed.

The thing you do to use SqlCacheDependency is to create a SqlCommand that contains the Sql-query you want to cache the result of.
Then you create a SqlCacheDependency with the SqlCommand as a parameter.

LinqToSql
It´s hard not to love LinqToSql... Sure, I know some DBA´s that do not love the thought of foreign code executing queries in there databases (if we ignore the option to use stored procedures with LinqToSql). I personally think that it´s ok to only use permissions in BLL on method level like:
PrincipalPermission(System.Security.Permissions.SecurityAction.Demand,Role"ADMIN",Authenticatedtrue)

The simplicity of development and the oppurtunity to quick changes in DAL and database makes it hard to resist LinqToSql :)

SqlCacheDependency with LinqToSql
Why cache LinqQueries?
Everytime you iterate over a LinqToSql-query it will execute...
Like -> foreach(MyObj o in MyLinqQuery) will execute the query against the SqlServer. One solution I´ve seen people using is: List = MyLiqToSqlQuery.ToList(); and then use cache on the result. This will ofcourse work, but you´ll not know if anything changes in the database unless you use SqlCacheDependency.

The challenge of LinqToSql & SqlCacheDependency is to extract the SqlQuery to be able to store it in a SqlCommand and then use the SqlCommand in the SqlCacheDependency. You will also have to provide functionality for parameters that the LinqQuery may hold... I created a generic ExtensionMethod for IQueryable to solve my problem.... one method to cache all LinqQueries

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

public static class Cache
{
///

/// Caches Linq query´s that is created for LinqToSql.
/// Limitations are the same as SqlCacheDependency
///

///
/// The linq query
/// Your LinqToSql DataContext
/// The unique Id for the cache
///
public static List LinqCache(this System.Linq.IQueryable q, System.Data.Linq.DataContext dc, string CacheId)
{
try
{
List objCache = (List)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);
// - removed 090506 - 7. Refresh the LinqQuery from DB so that we will not use the current Linq cache
// - removed 090506 - 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 on any Linq.IQueryable...