Why is second level cache not being used for a particular entity?

I am running NHibernate Profiler and I can't work out why some entities are being retrieved from the second level cache and some are not.

I have setup all the necessary things - 2nd level cache is enabled (SysCache2), and caching is set to Read开发者_StackOverflowOnly for the entities that are giving me problems. I am using transactions everywhere.

Firstly, my entities look like this:

Employee { Id, Name, CurrentOffice }

Office { Id, Name }

Team { Id, Name, Office }

Secondly, here are some example from the NH profiler to demonstrate the problem:

Session #1

The profile shows the following SQL call. This is generated by a Session.Get() call:

SELECT office_.Id, office_.Name FROM Office office_ WHERE office_.Id = 5

Session #2

In session 2, an employee is loaded, which then causes an eager select on his current office. The second level cache is correctly used, as the office was cached in Session #1:

SELECT employee.Id, employee_.Name, employee_.CurrentOfficeId FROM Employee employee_ WHERE employee_.Id = 1

2nd level cache load Office (5 /* id */)

Session #3

Next some Teams are loaded, which cause eager selects on their associated office. But now the 2nd level cache is not used for the office that we should have cached in the previous session.

SELECT team_.Id, team_.Name, team_.OfficeId FROM Team team_ WHERE team_.Id in (7,8,9)

SELECT office_.Id, office_.Name FROM Office office_ WHERE office_.Id = 5

Why does that the last SQL statement in Session #3 occur - why is the 2nd level cache not used, like it was in Session #2?

I've worked this out.

Within the code base a call was being made that used Session.CreateSqlQuery().ExecuteUpdate().

However, the query that it was executing had no entity specified. When this happens it appears that NHibernate decides to clear all cache regions, wiping out the entire cache!

Moral of the story, don't use Session.CreateSqlQuery().ExecuteUpdate() without specifying an entity.