How to get nhibernate to cache tables referenced v

Question

I've been trying to get this working for a while now with no luck. I want to enable the 2nd level cache to prevent fetching from some lookup tables.

I set up my configuration in code

cfg = new Configuration();
cfg.Properties[NHibernate.Cfg.Environment.ConnectionProvider] = "NHibernate.Connection.DriverConnectionProvider";
string connectionString = connection.ConnectionString;
cfg.Properties[NHibernate.Cfg.Environment.ConnectionString] = connectionString;
cfg.Properties[NHibernate.Cfg.Environment.ConnectionDriver] = "NHibernate.Driver.SqlClientDriver";
cfg.Properties[NHibernate.Cfg.Environment.Dialect] = "NHibernate.Dialect.MsSql2005Dialect";
cfg.Properties[NHibernate.Cfg.Environment.CommandTimeout] = "720";
cfg.Properties[NHibernate.Cfg.Environment.CacheProvider] = "NHibernate.Cache.HashtableCacheProvider";
cfg.Properties[NHibernate.Cfg.Environment.UseSecondLevelCache] = "true";
cfg.Properties[NHibernate.Cfg.Environment.UseQueryCache] = "true";
cfg.AddAssembly("APPName.PersistentEntities");
factory = cfg.BuildSessionFactory();

Then in my xml configuration I added the cache attribute:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="APPName.PersistentEntities.LockStatus, APPName.PersistentEntities" table="LockStatus" lazy="false">
    <meta attribute="class-description">lockstatus</meta>
    <cache usage="read-only" />
    <id name="Id" column="Id" type="Int32" unsaved-value="0">
      <generator class="native"></generator>
    </id>
    <property name="Name" column="Name" type="String" length="100" not-null="true"/>
  </class>
</hibernate-mapping>

This entity is then referenced from other uncached entities

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="APPName.PersistentEntities.Entity, APPName.PersistentEntities" table="Entity" lazy="false">
        <meta attribute="class-description">entity</meta>
        <id name="Id" column="Id" type="Int32" unsaved-value="0">
            <generator class="native">
            </generator>
        </id>

        <property name="Name" column="Name" type="String" length="500" not-null="true">
            <meta attribute="field-description">name</meta>
        </property>

        <many-to-one name="LockStatus" column="LockStatusId" class="APPName.PersistentEntities.LockStatus, APPName.PersistentEntities" not-null="false" lazy="false">
        </many-to-one>
    </class>
</hibernate-mapping>

>

I query this other entity with something like:

session = factory.OpenSession();
IList<T> s = session.CreateSQLQuery(query).AddEntity(typeof(T)).SetCacheable(true).List<T>();
session.Clear();
session.Close();

The query and mappings run fine so to make sure its using the cache I try updating the names in the database. When I click again in the application I see the updated names so I assume it is not using the cache re-querying.

Answer 1

you need to add a cache declaration on the relation as well (LockStatus).

also-

1. you can use nHibernate's logging to see exactly the sql sent on every call.
2. I don't see why you'd want to use an SQLQuery in your case; you can simply use Query or QueryOver.