CODEWITHSUNDEEP
c#asp.netnhibernate
Arunas
Arunas

Reputation: 918

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.

Upvotes: 1

Views: 1884

Answers (1)

Diego Mijelshon
Diego Mijelshon

Reputation: 52725

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.

Upvotes: 3

Related Questions