score:2

Accepted answer

Adding Cacheable before the count will cause the aggregate count results to be cached. This can be seen by the SQL generated from my example below.

Domain and Mapping Code

public class Entity
{
    public virtual long id { get; set; }
    public virtual string name { get; set; }
}

public class EntityMap : ClassMap<Entity>
{
    public EntityMap()
    {
        Id(x => x.id).GeneratedBy.Identity();
        Map(x => x.name);
        Cache.ReadOnly();
    }
}

The test code itself.

using (var session = NHibernateHelper.OpenSession())
using (var tx = session.BeginTransaction())
{
    session.Save(new Entity() { name = "Smith" });
    session.Save(new Entity() { name = "Smithers" });
    session.Save(new Entity() { name = "Smithery" });
    session.Save(new Entity() { name = "Smith" });
    tx.Commit();
}

String name_constant = "Smith";
using (var session = NHibernateHelper.OpenSession())
using (var tx = session.BeginTransaction())
{
    var result = session.Query<Entity>().Cacheable().Count(e => e.name == name_constant);
}

using (var session = NHibernateHelper.OpenSession())
using (var tx = session.BeginTransaction())
{
    var result = session.Query<Entity>().Cacheable().Count(e => e.name == name_constant);
}

The SQL generated from the above code can be seen below. As you can see, there are four INSERT statements, one for each session.Save. Whereas there is only one SELECT despite the two queries, each performing under a separate session. This is because the result of the count has been cached by NHibernate.

NHibernate: INSERT INTO [Entity] (name) VALUES (@p0); select SCOPE_IDENTITY();
@p0 = 'Smith' [Type: String (4000)]
NHibernate: INSERT INTO [Entity] (name) VALUES (@p0); select SCOPE_IDENTITY();
@p0 = 'Smithers' [Type: String (4000)]
NHibernate: INSERT INTO [Entity] (name) VALUES (@p0); select SCOPE_IDENTITY();
@p0 = 'Smithery' [Type: String (4000)]
NHibernate: INSERT INTO [Entity] (name) VALUES (@p0); select SCOPE_IDENTITY();
@p0 = 'Smith' [Type: String (4000)]
NHibernate: select cast(count(*) as INT) as col_0_0_ from [Entity] entity0_ 
            where entity0_.name=@p0;
@p0 = 'Smith' [Type: String (4000)]

The possible scenarios which will cause NHibernate to ignore Cacheable and go back to the DB are when:

  1. The second level cache is not enabled in the session factory configuration.
  2. The entity has not been marked as cacheable.

The only other scenario I know of that will cause NHibernate to perform two SELECT queries is when the entity has been evicted from the cache, either explicitly using sessionFactory.Evict or by the cached entity becoming expired between the two calls.


Related Articles