Lets say I use a query as below to get a large result set.
Select UserID,FirstName, LastName, City, Age from UserInfo where DateDiff(d,GetDate,LastActiveOn) > 180
Now, I want to cache each object returned by the query and invalidate the object when it changes. So I cast each result for the above result set to User objects (DTO) and then while adding them Cache, add the dependency as follows:
while (reader.Read())
{
var user = new UserInfo(reader, false);
float distance;
if (float.TryParse(reader["Distance"].ToString(), out distance))
{
user.Distance = distance;
}
//Add user to Cache
string userQuery = "SELECT UserID,FirstName,MiddleName,LastName FROM mydb.dbo.UserInfo where UserID=" + user.UserId.ToString();
var cacheDependencyCmd = new SqlCommand(userQuery, con);
SqlCacheDependency jobSeekerDependency = new SqlCacheDependency(cacheDependencyCmd);
TimeSpan timeLeftToExpiration = user.LastActive.AddDays(180) - DateTime.Now;
DateTime itemExpirationDate = DateTime.Now.AddDays(timeLeftToExpiration.Days);
string key = "u-" + user.UserId.ToString();
this.Context.Cache.Insert(key,user.UserId.ToString() + user.LastActiveString , jobSeekerDependency, itemExpirationDate, Cache.NoSlidingExpiration, CacheItemPriority.Normal, new CacheItemRemovedCallback(ItemRemovedCallBack));
But this doesn't seem to work. There are no records in sys.dm_qn_subscriptions. There are no more errors in Event Log (which I was getting yesterday and took appropriate steps to resolve those) Looking at the Sql Profiler I find that the SqlQueryNotification stored procedure is running every 2 minutes or so.
So I am wondering why has the dependency not been created? Is it because I am using a different query to create the dependency?
