Impact on NHibernate caching for searches with results including calculated value mapped as a formula (e.g. rank)

Question

When defining a calculated property using a formula in NHibernate, what are the implications for when the formula varies its result depending on the query restrictions, especially with regards to query caching?

More specifically, consider the following simple C# class:

public class Entity
{
    public Entity() { }
    public virtual int Id { get; protected set; }
    public virtual string Key { get; protected set; }
    public virtual string Value { get; protected set; }
    public virtual int Rank { get; protected set; }
}

Mapped with the following simple NHibernate mapping:

<class name="Entity" mutable="false">
    <id name="Id">
        <generator class="native">
    </id>
    <property name="Key"/>
    <property name="Value"/>
    <property name="Rank" formula="row_number() over(order by value)">
</class>

Running with a session factory with hibernate.cache.use_query_cache option set to true, and queried in the following ways:

ICriteria criteria = session.CreateCriteria(typeof(Entity));
criteria.SetCacheable(true);
criteria.SetCacheRegion("SearchResults");
IList<Entity> queryResult1 = criteria.List<Entity>();

criteria = session.CreateCriteria(typeof(Entity));
criteria.SetCacheable(true);
criteria.SetCacheRegion("SearchResults");
criteria.Add(Restrictions.Like("Key", "a", MatchMode.Anywhere));
IList<Entity> queryResult2 = criteria.List<Entity>();

Entity directResult = session.Load<Entity>(id);

Will NHibernate behave in a reasonable manner for the returned Entities? Or could the "Rank" value from one cached query pollute the Rank value of another query due to the query cache? Are there any other concerns when using such a formula in NHibernate mappings?

EDIT:

It might also be worth noting that in my particular case, "Entity" is not a first-class business entity, but sort of a meta-entity. It maps to an indexed database view over other first-class entities and is used exclusively for searching (the session.Load(id) call is contrived and should never actually happen in practice).

And, if there are implications to caching, as I suspect, what alternatives might exist for a similar use-case to avoid potential problems?

Answer 1

After further experimentation: Yes, there are cache implications that could result in inconsistent results; NHibernate cannot automatically know that the formula could change values between queries for entity results with the same identifier (and assumes it won't).

Having a class mapping as those in the question would result in the rank being stored with the rest of the entity data. This makes it possible that a subsequent query will end up returning a rank value from some other query rather than the query being run and thus have ranks that are not sequential as expected.

NHibernate has separate query and entity caches (there are actually two entity caches - the session cache and the second level entity cache) and the impacts depend on which ones are being used.

When the query cache is not enabled, incorrect rank values can be received if you make two different queries within the same session that share a result but with different ranks. In this case, the second query of the same session will not override the entity data already in the session from the first query (since it might have changed for that unit of work), so the rank value returned will be the same from the first query rather than the actual rank from the second query. Evicting the results from the first query should avoid this issue (but is not the recommended solution; see below)

When the query cache is enabled, incorrect rank values can also be received when repeating the same query after some other query has executed that had a result with a different rank. In this case, the first query execution adds the result identifiers to the query cache and the entities (with their rank) to the entity cache. The interleaved query (when run in another session) could result in a change to the rank value stored with the entity in the entity cache. When the first query is re-executed, the cached identifiers are used to lookup the cached entities (with the changed ranks).

---

The problem can be addressed completely by changing the entity to only include the persisted values for the entity (i.e. excluding the rank). Then, for the query, use a projection to extract the identifier and the rank for that query:

ICriteria criteria = session.CreateCriteria(typeof(Entity));
criteria.SetCacheable(true);
criteria.SetCacheRegion("SearchResults");
criteria.SetProjection
    (Projections.Id(), 
     Projections.SqlProjection("row_number() over(order by value) as Rank",
                               new[] { "Rank" },
                               new[] { NHibernateUtil.Int32 }));

In this case, since the rank is a value type, the query cache will store the rank along side the query result identifiers for that specific query. Then, using a second query, lookup the entity values using the projected identifiers. The tricky part is that you'll want to avoid an N+1 type issue when performing the entity query and you will need to create another data structure to marry the Entity and its associated rank for that query.

It's a little annoying that you have to use two separate queries rather than a single query, but this seems to be the only way to use the caches in an appropriate manner.