How to make OutputCache with SqlDependency dependent on a row in database for each request?

Question

I have a problem. Cannot find how to make OutputCache SQLDependency dependent on one row from database table. E.g. I have a Controller with one parameter.

ActionResult Index(int? id)

And for each request with same id I need to check database table table1(id int, last_updated datetime). If row with id = id and last_updated hasn't changed.

I use sql server 2005 or higher.

Which strategy should I use ?

I tried to use:

[OutputCache(Duration = int.MaxValue, VaryByParam = "id", 
    SqlDependency = "DatabaseName:table1")]

but it works for whole table changes.

Answer 1

To avoid hitting the database for each webpage request (generally an expensive operation), Object Caching (introduced in .NET 4.0) can be used. This would result in fast serving of webpages because everything would be handled directly from memory. Database operations would occur only if the data actually changed, or if the cache was evicted from memory due to resource constraints or your CacheItemPolicy settings.

The practical strategy used in this case would be as follows.

Step-1. In your Model method where data for the "id" row is being modified/added/deleted, complete that database operation and then:

• If your object already exists in the cache, evict it;
• Create a new cache entry for the object.

Step-2. Retrieve the object from cache whenever possible, refreshing from the db only if necessary:

• From your Controller action method, call a Model method that returns the object identified by the "id" parameter;
• Within your Model method, check the cache for this id. If it is null, retrieve the data from your database and build the object as you would normally do, and then store the complete object in the cache;
• From the Model method, return the content of the cache (i.e. your specific object for this id) back to the calling Controller action method, and then let the action method populate and serve the View as usual.

(The MemoryCache class is the concrete implementation of the ObjectCache class).

With this approach, the OutputCache on the Controller method would not need to be used at all, and the data caching decisions would all be fully encapsulated within the Model. We would get a cleaner separation of concerns; much higher efficiency, better response times, and improved scalability; and a reduced dependency on costly database operations.