Entities returned with ISQLQuery are not stored in NHibernate second level cache?

I am trying to use second level cache for lookup entities because there are only a开发者_高级运维 few of them and these entities should be changed only in exceptional cases.

If I fetch these entities with ICriteria then these entities are placed in the second level cache however if I fetch these via ISQLQuery (table-value-function) then the entities are not cached. Calling .SetCacheable(true) etc has no effect.

So, does second level caching with ISQLQuery is not supported or I am missing some configuration setting?

Edit:

Example - first query in the whole app after IIS restart:

var query = _session.CreateSQLQuery("SELECT {foo.*} fro dbo.foo_GetList()");
query.AddEntity("foo", typeof(Foo));
query.SetCacheable(true);
query.List<Foo>();

When the above code runs I see nothing added into the cache (e.g. no NHibernate.Caches.SysCache.SysCache - adding new data within NHibernate log). Subsequent calls such as _session.Get(id) do a new select. If I replace the following with

var criteria = _session.CreateCriteria<Foo>();
criteria.List<Foo>();

then entities are cached.

Furthermore it seems for me when using ISQLQuery returned objects don't pick dependent entities from the cache but select them anew.

It works fine for me. Here's a proof of concept:

Class:

public class Foo
{
    public virtual int Data { get; set; }
}

Mapping:

<class name="Foo">
  <cache usage="read-write" />
  <id type="Guid">
    <generator class="guid.comb" />
  </id>
  <property name="Data" />
</class>

Test Code:

        using (var session = sessionFactory.OpenSession())
        using (var tx = session.BeginTransaction())
        {
            session.Save(new Foo { Data = 1 });
            tx.Commit();
        }
        using (var session = sessionFactory.OpenSession())
        using (var tx = session.BeginTransaction())
        {
            session.CreateSQLQuery("select {foo.*} from {foo}")
                   .AddEntity("foo", typeof(Foo))
                   .SetCacheable(true)
                   .List();
            tx.Commit();
        }
        using (var session = sessionFactory.OpenSession())
        using (var tx = session.BeginTransaction())
        {
            session.CreateSQLQuery("select {foo.*} from {foo}")
                   .AddEntity("foo", typeof(Foo))
                   .SetCacheable(true)
                   .List();
            tx.Commit();
        }

The second session does not result in any DB hits.

Make sure you have configured a cache provider and enabled both the query cache and caching for your entity.