Caching LinqToSql results with SqlCacheDependency

UPDATED WIKI: Home

UTB — Wed, 09 Jul 2008 09:00:07 GMT

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

UPDATED WIKI: Home

UTB — Wed, 09 Jul 2008 08:59:17 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches Linq query that is created for LinqToSql.
        /// Limits are the same as SqlCacheDependency + some unresolved issues with where statements
        /// </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

IMPORTANT ISSUE

I´ll need to admit that I´ve not tested this for any purpose other then my own... But while writing this I realised that
you cant have queries with "where" statements. Maybe not a big deal because I do not know a purpose to cache just one or a few objects from a DB.
However you can select the record from the cache filled with List<T> using Linq...

Happy Coding

UTB

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 19:29:26 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches Linq query that is created for LinqToSql.
        /// Limits are the same as SqlCacheDependency + some unresolved issues with where statements
        /// </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.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.2 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

IMPORTANT ISSUE

Happy Coding

UTB

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 19:25:57 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches Linq query that is created for LinqToSql.
        /// Limits are the same as SqlCacheDependency + some unresolved issues with where statements
        /// </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.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.2 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

IMPORTANT ISSUE

Happy Coding

UTB

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 19:23:19 GMT

What´s this?

IMPORTANT ISSUE

I´ll need to admit that I´ve not tested this for any purpose other then my own... But while writing this I realised that
you cant have queris with where statements. Maybe not a big deal because I always cache everything and not just one record.
With linq you can select the record from the cache using Linq

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches Linq query that is created for LinqToSql.
        /// Limits are the same as SqlCacheDependency + some unresolved issues with where statements
        /// </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.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.2 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

Happy Coding

UTB

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 18:58:29 GMT

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, bur it´s always fun if someone else can use (or comment, good or bad) the code.

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches any 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.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.2 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");

Calling the Extension Method

Happy Coding

UTB

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 18:55:53 GMT

Resource Page Description
Building a extensionmethod for caching LINQ results with SqlCacheDependency

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches any 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.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.2 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");

Calling 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 extension method to really see that we collect from the cache until a row is changed in the DB that would invalidate our LinqQuery.

**Delete the following note before publishing **

This resource page is currently in setup mode and only available to coordinators and developers. Once you have finished setting up your resource page you can publish it to make it available to all MSDN Code Gallery visitors.

To get your Resource Page ready to publish, you should do the following:

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 18:51:17 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches any 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.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.2 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");

Calling the Extension Method

203430687.png
Resource Page Description
Building a extensionmethod for caching LINQ results with SqlCacheDependency

**Delete the following note before publishing **

This resource page is currently in setup mode and only available to coordinators and developers. Once you have finished setting up your resource page you can publish it to make it available to all MSDN Code Gallery visitors.

To get your Resource Page ready to publish, you should do the following:

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 18:50:37 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches any 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.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.2 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");

Calling the Extension Method

Cannot resolve link: [file:_203430687.png_]
Resource Page Description
Building a extensionmethod for caching LINQ results with SqlCacheDependency

**Delete the following note before publishing **

This resource page is currently in setup mode and only available to coordinators and developers. Once you have finished setting up your resource page you can publish it to make it available to all MSDN Code Gallery visitors.

To get your Resource Page ready to publish, you should do the following:

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 18:48:07 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches any 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.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.2 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");

Resource Page Description
Building a extensionmethod for caching LINQ results with SqlCacheDependency

**Delete the following note before publishing **

This resource page is currently in setup mode and only available to coordinators and developers. Once you have finished setting up your resource page you can publish it to make it available to all MSDN Code Gallery visitors.

To get your Resource Page ready to publish, you should do the following:

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 18:42:25 GMT

What´s this?

Caching Linq results using SqlCacheDependency

    public static class Cache
    {
        /// <summary>
        /// Caches any 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.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.2 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;
            }
        }
    }

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 13:50:11 GMT

//multiline

public static void JustATest()
{
   try
   {
      //code here
   }
   catch(Exception ex)
   {
      throw ex,
   }
}

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 13:48:48 GMT

//multiline

public static void JustATest()
{
try
{
code here
}
catch(Exception ex)
{
throw ex,
}
}

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.

UPDATED WIKI: Home

UTB — Tue, 08 Jul 2008 13:16:56 GMT

//multiline

void context_Error(object sender, EventArgs e)
    {
        try
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
 
            if (!System.IO.File.Exists(this.oApplication.Context.Request.PhysicalApplicationPath + "\\" + this.fileErr))
            {
                this.swErr = new System.IO.StreamWriter(this.oApplication.Context.Request.PhysicalApplicationPath + "\\" + this.fileErr, false);
                this.swErr.WriteLine("TIME;REQUESTTYPE;ERRORMESSAGE;EXCEPTION;BASEEXCEPTION;URL;QUERYSTRING;CONTENTTYPE;USERAGENT;USERHOSTADRESS;USERHOSTNAME");
            }
            else
                this.swErr = new System.IO.StreamWriter(this.oApplication.Context.Request.PhysicalApplicationPath + "\\" + this.fileErr, true);
            sb.Append(DateTime.Now.ToString() + ";");
            sb.Append(this.oApplication.Context.Request.RequestType + ";");
            sb.Append(this.oApplication.Context.Error.Message.Replace(";", "|") + ";");
            sb.Append(this.oApplication.Context.Error.GetType().FullName + ";");
            sb.Append(this.oApplication.Context.Error.GetBaseException().GetType().FullName + ";");
            sb.Append(this.oApplication.Context.Request.Url + ";");
            sb.Append(this.oApplication.Context.Request.QueryString + ";");
            sb.Append(this.oApplication.Context.Request.ContentType + ";");
            sb.Append(this.oApplication.Context.Request.UserAgent.Replace(";", "|") + ";");
            sb.Append(this.oApplication.Context.Request.UserHostAddress + ";");
            sb.Append(this.oApplication.Context.Request.UserHostName + ";");
 
            this.swErr.WriteLine(sb.ToString());
            this.swErr.Flush();
            this.swErr.Close();
            this.swErr.Dispose();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Make any changes to the details of your resource page

Here you can enable or disable functions of your resource page. You might want to turn on the Issue Tracker to allow users to provide feedback on your resource, or if you have a resource that does not involve a code sample, you may want to turn off the Releases tab.
Make sure your resource page description is detailed enough to let people search for your resource.

Add your code sample or other resources to the resource page

If you’re uploading code, go to the Releases tab and create a new release to house your code. Creating a release allows you to have the license properly displayed when people download your code, as well as provides a download count.
Edit your Wiki page to attach any resources you may have that are not source code.

If you want to let someone see your resource page before it is published, go to the People tab and add them to your resource page

This will let you add other team members who may be contributing to your resource, or just show it off and get feedback from someone you trust.

Tag your resource page with descriptive tags to make it easier for people to find your resources when browsing the gallery.
Publish your resource page so it becomes visible to everyone!

Additional information on starting a new resource page is available here: Resource Page Startup Guide.