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
{
/// <summary>
/// Caches Linq query´s that is created for LinqToSql.
/// Limitations 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);
// - 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<T> on any Linq.IQueryable<T>...
Check the image below for an example from my project.
203430687.pngYou 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