title: How do I make NHibernate cache fetched child collections?
body: <p>I've got a fairly simple criteria query that fetches child collections, like so:</p>
  
  <pre><code>var order = Session.CreateCriteria&lt;Order&gt;()
      .Add(Restrictions.Eq("Id", id))
      .SetFetchMode("Customer", FetchMode.Eager)
      .SetFetchMode("Products", FetchMode.Eager)
      .SetFetchMode("Products.Category", FetchMode.Eager)
      .SetCacheable(true)
      .UniqueResult&lt;Order&gt;();
  </code></pre>
  
  <p>Using NH Prof, I've verified that this makes just one round trip to the database (as expected) with a cold cache; however, on successive executions, it retrieves only the <code>Order</code> from the cache and then hits the database with a SELECT(N+1) for every child entity in the graph, as in:</p>
  
  <pre><code>Cached query: SELECT ... FROM Order this_ left outer join Customer customer2 [...]
  SELECT ... FROM Customer WHERE Id = 123;
  SELECT ... FROM Products WHERE Id = 500;
  SELECT ... FROM Products WHERE Id = 501;
  ...
  SELECT ... FROM Categories WHERE Id = 3;
  </code></pre>
  
  <p>And so on and so forth. Clearly it's not caching the <em>whole</em> query or graph, only the root entity. The first "cached query" line actually has all of the <code>join</code> conditions that it's supposed to - it's definitely caching the <em>query itself</em> correctly, just not the <em>entities</em>, apparently.</p>
  
  <p>I've tried this using the SysCache, SysCache2, and even HashTable cache providers and I always seem to get this same behaviour (NH version 3.2.0).</p>
  
  <p>Googling turned up a number of ancient issues, such as:</p>
  
  <ul>
  <li><a href="https://nhibernate.jira.com/browse/NH-195" rel="nofollow noreferrer">NH-195: Child collections are not being stored in the second level cache</a></li>
  <li><a href="https://forum.hibernate.org/viewtopic.php?p=2368642&amp;sid=80db14f4554220de0bcbef3e84b58399" rel="nofollow noreferrer">Syscache2 2nd level cache: Child coll. objects requeried</a></li>
  <li><a href="https://forum.hibernate.org/viewtopic.php?p=2368641#2368641" rel="nofollow noreferrer">Weird differences between SysCache and SysCache2</a></li>
  <li><a href="http://ayende.com/blog/4046/nhibernate-beware-of-inadvisably-applied-caching-strategies" rel="nofollow noreferrer">NHibernate – Beware of inadvisably applied caching strategies</a> (Ayende - of course he only bothers to mention what <em>not</em> to do, not how to fix it...)</li>
  </ul>
  
  <p>However, these all seem to have been fixed a long time ago, and I get the same bad behaviour regardless of which provider I use.</p>
  
  <p>I've read through the <a href="http://nhibernate.info/doc/nhibernate-reference/caches.html#NHibernate.Caches.SysCache2" rel="nofollow noreferrer">nhibernate.info documentation on SysCache and SysCache2</a> and there doesn't seem to be anything I'm missing. I've tried adding <code>cacheRegion</code> lines to the <code>Web.config</code> file for all tables involved in the query, but it doesn't change anything (and AFAIK those elements are just to <em>invalidate</em> the cache, so they shouldn't matter anyway).</p>
  
  <p>With all of these super-old issues that all seem to be fixed/resolved, I figure this can't possibly <em>still</em> be a bug in NHibernate, it must be something that I'm doing wrong. But what?</p>
  
  <p>Is there something special I need to do when combining fetch instructions in NHibernate with the second-level cache? What am I missing here?</p>