Cache and SqlCacheDependency (ASP.NET MVC)

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

Running into the same issue and finding the same answers online without any help, I was reasearching the xml invalid subsicription response from profiler.

I found an example on msdn support site that had a slightly different order of code. When I tried it I realized the problem - Don't open your connection object until after you've created the command object and the cache dependency object. Here is the order you must follow and all will be good:

1. Be sure to enable notifications (SqlCahceDependencyAdmin) and run SqlDependency.Start first
2. Create the connection object
3. Create the command object and assign command text, type, and connection object (any combination of constructors, setting properties, or using CreateCommand).
4. Create the sql cache dependency object
5. Open the connection object
6. Execute the query
7. Add item to cache using dependency.

If you follow this order, and follow all other requirements on your select statement, don't have any permissions issues, this will work!

I believe the issue has to do with how the .NET framework manages the connection, specifically what settings are set. I tried overriding this in my sql command test but it never worked. This is only a guess - what I do know is changing the order immediately solved the issue.

I was able to piece it together from the following to msdn posts.

This post was one of the more common causes of the invalid subscription, and shows how the .Net client sets the properties that are in contrast to what notification requires.

https://social.msdn.microsoft.com/Forums/en-US/cf3853f3-0ea1-41b9-987e-9922e5766066/changing-default-set-options-forced-by-net?forum=adodotnetdataproviders

Then this post was from a user who, like me, had reduced his code to the simplest format. My original code pattern was similar to his.

https://social.technet.microsoft.com/Forums/windows/en-US/5a29d49b-8c2c-4fe8-b8de-d632a3f60f68/subscriptions-always-invalid-usual-suspects-checked-no-joy?forum=sqlservicebroker

Then I found this post, also a very simple reduction of the problem, only his was a simple issue - needing 2 part name for tables. In his case the suggestion resolved the issue. After looking at his code I noticed the main difference was waiting to open the connection object until AFTER the command object AND the dependency object were created. My only assumption is under the hood (I have not yet started reflector to check so only an assumption) the Connection object is opened differently, or order of events and command happen differently, because of this association.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bc9ca094-a989-4403-82c6-7f608ed462ce/sql-server-not-creating-subscription-for-simple-select-query-when-using-sqlcachedependency?forum=sqlservicebroker

I hope this helps someone else in a similar issue.