title: SQL Caching and Entity Framework
body: <p>I have put together a small ASP.NET MVC 2 site that does some very extensive date mining/table-joins/etc.  </p>
  
  <p>Using MVC, I have a controller that returns the data in many different forms (tables, images, etc).  To save hitting the database frequently I have a dual cache mechanism: </p>
  
  <ol>
  <li>For identical parameters to the same action I use the <code>OutputCacheAttribute</code> with <code>VaryByParam = "*"</code>.  </li>
  <li>Assuming some parameter to the action has changed (or another action is called), it is still possible that my "data" has previously been requested, so I store the data in a view model after the first hit of the database, I achieve this with a .NET 4.0 <code>System.Runtime.Caching.ObjectCache</code>.</li>
  </ol>
  
  <p>Example of the <code>ObjectCache</code> inside of the Controller:</p>
  
  <pre><code>private static readonly ObjectCache cache = 
        new MemoryCache("CompareControllerCache");
  private static void CacheObject(ViewModel obj, 
                                  string param1, 
                                  int someOtherParam )
  {
      string key = string.Format("{0}-{1}", param1, someOtherParam);
      Trace.WriteLine(string.Format("Adding {0} to the cache", key));
      cache.Add(key, obj, new CacheItemPolicy
           {
               SlidingExpiration = TimeSpan.FromMinutes(1)
           });
  }
  
  // Corresponding GetCachedObject with similar key defining logic.
  </code></pre>
  
  <p>This gives me a good performance improvement, but where it fails is on the <code>CacheItemPolicy</code> being very simple.  Ideally, I'd like the cache-window to be bigger but have the cached item expire if the database changes.</p>
  
  <p>The <code>CacheItemPolicy</code> seems to support this with the <code>ChangeMonitors</code> collection, to which I could add a <code>SqlChangeMonitor</code>, but when trying to construct this is where I come to a halt.</p>
  
  <p>I am using Entity Framework 4 to access an SQL Database, how <strong>how do I construct the <code>SqlChangeMonitor</code> to monitor the couple of database tables that are likely to trigger a cache expire?</strong></p>
  
  <p><code>SqlChangeMonitor</code> is constructed with an <code>SqlDependency</code> which takes an <code>SqlCommand</code> - how can I latch upon Entity Framework's encapsulation of my database?</p>