CODEWITHSUNDEEP
nhibernatesql-order-byquery-cache
Oliver
Oliver

Reputation: 9508

NHibernate retrieves cached query results even though the ORDER BY clause differs

I'm seeing what I would call a bug in NHibernate with query caching and ORDER BY clauses...

When I run the following query...

SELECT this_.Id          as y0_,
       this_.Name        as y1_
FROM   Products this_
WHERE  this_.IsActive = 1
ORDER BY this_.IsPremium desc

... NHibernate successfully caches its results if I have query caching turned on and I tell it to cache this query (using e.g. criteria.SetCacheable(true)).

Unfortunately, as the wonderful NHProf tells me, NHibernate uses the cached query results also when running this query:

SELECT this_.Id          as y0_,
       this_.Name        as y1_
FROM   Products this_
WHERE  this_.IsActive = 1
ORDER BY this_.IsPremium desc,
         this_.Name

Can anybody explain why or point me to some in-depth documentation on this "feature"? Or, even better, does anyone have a solution to the problem?

Upvotes: 2

Views: 1080

Answers (1)

TedOnTheNet
TedOnTheNet

Reputation: 1102

NHibernate uses first level caching (implemented by an identity map) to cache every object you query for. NHibernate works on single entities, even if you query a list of objects.

Let me explain some stuff:
you query if you execute this query and try to get entity with id 1: 

session.get<SomeEntity>(1);

NHibernate will check the cache to see if it contains id 1 already. If so, the cached object will be returned and it will not run a query. if not, the query is executed selecting the record, put it in the cache and return it to you. If you execute the query again, it's cached and the object will be returned without a new query.

now, if you query a list like this: 

session.QueryOver<SomeEntity>().List();

nhibernate does not know which id's will be fetched, thus it will query all records and check the results against the cache one by one, even if you run the query twice. Lets say you have 2 records (id 1 and 2) in your database. You fetch them with the query while your cache is still empty. both records are fetched, put into the cache and returned to you. now you insert records 3, 4 and 5 and while your at it, you will update record 1 as well. Now, if you run the query again it will read all 5 records, but now cache records 3, 4 and 5 as well. You will get a list with 5 objects of which 3, 4, 5 are the ones just read, but the cached versions of 1 and 2 are returned. you will not get the updated version of id 1.

thus to answer your question: changing the order-by doesn't matter. your query will result in a set of records which are checked against the cache one by one, and if one of them exists already, this cached version is returned.

solutions to your problems might be:

  • using session.Refresh(obj); if you know which ones have been updated.
  • evict some or all entities from the session (which means they'll be thrown out of the cache and can be refetched with a query). note: if you evict them, changed won't be saved.
  • you can use a stateless session, which has - as the stateless name suggests - no cache.

Upvotes: 0

Related Questions