title: NHibernate SysCache2 and the Query Cache - Cannot avoid a Select N+1?
body: <p>I'm using the second level cache provider SysCache2 with Fluent NHibernate, using a standard fluent configuration (with query cache enabled as seems to be the general advice), and having table based dependencies defined in the usual manner.</p>
  
  <p>Fluent:</p>
  
  <pre class="lang-cs prettyprint-override"><code>config.Cache(c =&gt; c
   .ProviderClass&lt;NHibernate.Caches.SysCache2.SysCacheProvider&gt;()
   .UseQueryCache()
   .UseSecondLevelCache()
   .UseMinimalPuts()
  </code></pre>
  
  <p>)</p>
  
  <p>Web.Config:</p>
  
  <pre class="lang-cs prettyprint-override"><code>  &lt;cacheRegion name="User" relativeExpiration="7200"&gt;
  &lt;dependencies&gt;
    &lt;tables&gt;
      &lt;add name="User" databaseEntryName="OldClient" tableName="tbl_users" /&gt;
    &lt;/tables&gt;
  &lt;/dependencies&gt;
  </code></pre>
  
  <p></p>
  
  <p>User Mapping:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class UserMap : ClassMap&lt;User&gt;
  {
      public UserMap()
      {
          Table("Users");
          ....
          // caching
          Cache.IncludeAll().ReadWrite().Region("Users");
      }
  }
  </code></pre>
  
  <p>Everything runs as expected for single requests, ie:</p>
  
  <pre class="lang-cs prettyprint-override"><code>Session&lt;User&gt;.Get()
  </code></pre>
  
  <p>as would be expected from the way that <a href="http://ayende.com/blog/3988/nhibernate-the-difference-between-get-load-and-querying-by-id" rel="nofollow noreferrer">NHibernate caches by ID</a>. Subsequent requests don't hit the database, and invalidating the record in the database causes the Entity to be invalidated, resulting in a subsequent SQL call during the next request. All ok.</p>
  
  <p>The problem is with query caching. In the first instance, everything works fine. Executing a call such as:</p>
  
  <pre class="lang-cs prettyprint-override"><code>Session&lt;User&gt;.Query(item =&gt; item.Active == true) 
  </code></pre>
  
  <p>results in a SQL call as you would expect (SELECT * FROM Users WHERE Active = true). And subsequent executions result in no SQL. Great. Until such a time when a single record from the query set is changed in the database. Executing the same query then results in a SELECT N+1:</p>
  
  <pre class="lang-cs prettyprint-override"><code>SELECT * FROM Users WHERE ID = 1
  SELECT * FROM Users WHERE ID = 2
  SELECT * FROM Users WHERE ID = 3
  SELECT * FROM Users WHERE ID = 4
  ...
  </code></pre>
  
  <p>I've found references to this elsewhere, although no solutions:</p>
  
  <p><a href="https://stackoverflow.com/questions/8761249/how-do-i-make-nhibernate-cache-fetched-child-collections">StackOverflow - How do I make NHibernate cache fetched child collections?</a> See the 'one more point' section</p>
  
  <p><a href="http://ayende.com/blog/4046/nhibernate-beware-of-inadvisably-applied-caching-strategies" rel="nofollow noreferrer">Ayende Caching Strategies</a> he mentions ensuring 'entities are also cached' at the end</p>
  
  <p>The only way I can avoid this at the moment is to clear the query cache after every request - which almost renders it useless. What I need to for the query cache to clear everything for a particular Entity when that entity is invalidated - not just the individual records.</p>
  
  <p>Any ideas?</p>