title: Using SQLDependency or SqlCacheDependency with Repository pattern
body: <p>I am building an ASP.NET MVC application using the Repository pattern. A typical method in my Repository is as follows</p>
  
  <pre><code>   public IList&lt;T&gt; Select&lt;T&gt;(string cacheKey, string Sql, object filter) where T: new()
      {
          IList&lt;T&gt; items = MemoryCache.Default.Get(cacheKey) as IList&lt;T&gt;; 
            if (items == null || !items.Any())
            {
                items = Connection.Select&lt;T&gt;(Sql, filter);
                MemoryCache.Default.Add(cacheKey, items, DateTime.Now.AddMinutes(120));
            }
  
          return items;
  
      }
  </code></pre>
  
  <p>and it is being used as follows </p>
  
  <pre><code>IEnumerable&lt;OSADCOL&gt; osadcols = Repository.Select&lt;OSADCOL&gt;("OSADCOLS__TblId=" + Id, "TBLid = @id", new { id = Id });
  </code></pre>
  
  <p>In the above mentioned example OSADCOL is a model in my app and represents a Table with the same name in my Database. The <code>Connection.Select</code> function is ORMlite function. I don't use Entity Framework for performance issues. My problem is the following. I am currently cashing the result set for future relevance but I am doing it in a hard coded way. I am caching the result set for 2 hours. It is obvious that a proper implementation would be to discard my cashed data when the OSADCOL's table data changes. It seems that I have to use SQLDependency or SQLCacheDependency. The questions are the following:</p>
  
  <ol>
  <li>How am I going to use SQLDependency or SQLCacheDependency with this Repository?</li>
  <li>What is the actual difference between these 2?</li>
  <li>It is mentioned in some forums that SQLDependency creates memory leaks? Is that true? And If yes is there an alternative approach?</li>
  </ol>
  
  <p>Any Ideas?</p>