I am back with some solutions. I will show you the working solutions first,
then I'd explain why our original code does not work.
Solution 1. We need to execute the SqlCommand that we used to create the
dependency. After then, we can use DataContext.Translate to create the
entities. Here is a working sample in C#:
public class PeopleCache
static string constr =
static string cacheKey = "personsCacheKey";
static void OnCacheRemoved(string key, object value,
System.Diagnostics.Trace.WriteLine("People Cache Invalidated");
static IList<People> GetAndCachePeople()
using (SqlConnection con = new SqlConnection(constr))
using (DataClassesDataContext db = new DataClassesDataContext(con))
var query = from p in db.Peoples select p;
SqlCommand cmd = (SqlCommand)db.GetCommand(query);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
using (var rdr = cmd.ExecuteReader())
people = db.Translate<People>(rdr).ToList();
HttpContext.Current.Cache.Add(cacheKey, people, dependency,
public IList<People> GetPeople()
IList<People> people =
if (people == null)
people = GetAndCachePeople();
Solution 2. Use CallContext.SetData to register a SqlDependency object, and
in its NotificationCallback, we clear the cache.
One reason for the failure of our original code is that the cmd object
registered to the dependency object and the cmd used for execute are
SqlCommand cmd1 = db.GetCommand(query) as SqlCommand;
SqlCommand cmd2 = db.GetCommand(query) as SqlCommand;
bool result = cmd1.Equals(cmd2);
This codesnippet returns false, and thus, the it failed to get the cache
invalidity notification to work.
Let me know if you have any other questions.
Jialiang Ge (jialge@xxxxxx, remove 'online.')
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
This posting is provided "AS IS" with no warranties, and confers no rights.