NHibernate named query and 2nd level cache

Question

Hi all,

I have the following mapping

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               assembly="Domain.Core"
               namespace="Domain.Core">
<class name="Industry" table="INDUSTRY">
<cache usage="read-write" region="IndustryCache" include="all"/>
<id name="Id" type="Int64" column="IID">
  <generator class="sequence">
    <param name="sequence">INDUSTRY_SEQ</param>
  </generator> 
</id>
<version name="Version" column="VERSION" access="property" unsaved-value="null" generated="never"/>
<property name="CreationTime" column="CREATE_DATE" type="DateTime" not-null="true" />
<property name="CreatedBy" column="CREATE_USER" type="String" not-null="true" />
<property name="LastUpdateTime" column="MODIFY_DATE"  type="DateTime" not-null="false" />
<property name="LastUpdateBy" column="MODIFY_USER" type="String" not-null="false" />
<property name="Code" column="INDUSTRY" type="String" not-null="false" />
<map name="Resources" table="INDUSTRY_TL"  fetch="subselect">
  <cache region="IndustryCache" usage="read-write" include="all"/>
  <key column="INDUSTRY_ID"/>
  <composite-index class="Framework.Globalization.UILanguage, Framework">
    <key-property name="Code" column="LANG" access="property" />
  </composite-index>
  <composite-element class="Industry+Translation">
    <property name="Name" column="Industry_TL" />
  </composite-element>
</map>
</class>
<query name="GetIndustyOrderByName">
<![CDATA[
from Industry as i left join fetch i.Resources as res where INDEX(res) = :lang order  
by res.Name
]]>
</query>    
</hibernate-mapping>

and the following configuration in hibernate.cfg.xml

<property name="show_sql">true</property>
<property name='proxyfactory.factory_class'>NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
<property name='prepare_sql'>true</property>
<property name='query.substitutions'>Y=true,N=false,0=false,1=true</property>
<property name="generate_statistics">true</property>
<property name="cache.use_second_level_cache">true</property>
<property name="cache.provider_class">Framework.Cache.SossCacheProvider, Framework.Cache.SossNHibernateCache</property>
<property name="cache.use_query_cache">true</property>

Now when I run the named query with SetCacheable(true) which calls out to the mapped collection, it doesn't get to the 2nd level cache. Is there any reason why?

More Generically, is there a way to put the resultset of a named query into second level cache?

Thanks!

Answer 1

In order for queries to use the second level cache, two things must be done:
1. Enable the query cache in the NHibernate config:

    <property name="cache.use_query_cache">true</property>

2. Enable the query for caching when getting the IQuery instance:

    IQuery = session.GetNamedQuery("from Industry as i left join fetch i.Resources as res where INDEX(res) = :lang order by res.Name")
      .SetCacheable(true)
      .Setxxx();

These settings cause the results of the queries to be put in the second level cache. But the second level query cache stores only identifiers of entities, not the entities themselves. In order for the execution of the query to avoid the database completely, the entities must be cached as well. See the NH Docs for more explanation of the interaction of the second level caches.