Cache and SqlCacheDependency (ASP.NET MVC)

Question

We need to return subset of records and for that we use the following command:

using (SqlCommand command = new SqlCommand(
                    "SELECT ID, Name, Flag, IsDefault FROM (SELECT ROW_NUMBER() OVER (ORDER BY @OrderBy DESC) as Row, ID, Name, Flag, IsDefault FROM dbo.Languages) results WHERE Row BETWEEN ((@Page - 1) * @ItemsPerPage + 1) AND (@Page * @ItemsPerPage)",
                    connection))

I set a SqlCacheDependency declared like this:

SqlCacheDependency cacheDependency = new SqlCacheDependency(command);

But immediately after I run the command.ExecuteReader() instruction, the hasChanged base property of the SqlCacheDependency object becomes true although I did not change the result of the query in any way! And, because of this, the result of this query is not kept in cache.

HttpRuntime.Cache.Insert( cacheKey, list, cacheDependency, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(AppConfiguration.CacheExpiration.VeryLowActivity));

Is it because the command has 2 SELECT statements? Is it ROW_NUMBER()? If yes, is there any other way to paginate results?

Please help! After too many hours, a little will be greatly appreciated! Thank you

Answer 1

Just a guess, but could it be because your SELECT statement doesn't have an ORDER BY clause?

If you don't specify an explicit ordering then it's possible for the query to return the results in any order each time it is run. Maybe this is causing the SqlCacheDependency object to think that the results have changed.

Try adding an ORDER BY clause:

SELECT ID, Name, Flag, IsDefault
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY @OrderBy DESC) AS Row,
        ID, Name, Flag, IsDefault
    FROM dbo.Languages
) AS results
WHERE Row BETWEEN ((@Page - 1) * @ItemsPerPage + 1) AND (@Page * @ItemsPerPage)
ORDER BY Row

Answer 2

Fabio, have you ever been to figure out this issue?? I have the same identical problem that you are having and picking my brains out. Pleas share if you have.

Thanks!

Answer 3

i'm no expert on SqlCacheDependency, in fact, i found this question whilst looking for answers to my own issues with it! However, i believe the reason your SqlCacheDependency is not working is because your SQL contains a nested sub query.

Take a look at the documentation which lists what you can/can not use in your SQL: Creating a Query for Notification

"....The statement must not contain subqueries, outer joins, or self-joins....."

I also found some invaluable troubleshooting info from a guy at Redgate here: Using and Monitoring SQL 2005 Query Notification that helped me solve my own problem: By using Sql Profiler to trace the QN events he suggests, i was able to spot my connection was incorrectly using the 'SET ARITHABORT OFF' option, causing my notifications to fail.