It's never been 100% clear to me when using second level cache with NHibernate, when NHibernate accesses the cache and when it needs to go to the database directly. There is a great explanation of how NHibernate uses caching in The NHibernate FAQ, but even after reading this, I still wasn't completely clear on when NHibernate has to hit the database, so I went about testing it out myself. Below are the results of my testing; I was using NHibernate 2.1.2.400, FluentNHibernate 1.0.0.629, SQLServer 2008 and NCache 3.6. I created a single test consisting of each of the code snippets below and I set the NHibernate show_sql configuration parameter to true so that I could see when NHibernate hit the database. I also created an IClassConvention that tells NHibernate to cache all entities as read write (in reality I would probably be more granular than this).

The first thing I did was do a simple read. The cache started off empty and my database was seeded with data, so I expected this to hit hit the database.

const int teamId = 1;

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    session.Get<FootballTeam>(teamId);
}

And sure enough, that's exactly what it did. Stepping through this code I can saw a select statement output when the Get method is executed and I saw an object appear in the cache. The next part of the test runs the same piece of code again.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    session.Get<FootballTeam>(teamId);
}

This time because football team 1 is already in the cache, Nhibernate doesn't need to round trip to the database, it can simply read football team 1 from the cache, which is the whole point of using second-level cache in the first place! The next test is to see what happens when we access one of football team's children.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    FootballTeam team = session.Get<FootballTeam>(teamId);
    int count = team.Coaches.Count;
}

The code is almost nonsense but it serves the purpose of forcing NHibernate to load the Coaches list. There is a one to many relationship between Football teams and coaches and, as is common practice with one to many relationships, they are configured to be loaded lazily. Accessing the Count property of the list requires NHibernate to load the coaches. Since this is the first time we are accessing the coaches NHibernate has to go to the database to read them. As we did previously, we then repeat this code.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    FootballTeam team = session.Get<FootballTeam>(teamId);
    int count = team.Coaches.Count;
}

Once again NHibernate queries the database. This is despite me creating a convention telling NHibernate to put all entities in second-level cache. If you refer back to The NHibernate FAQ the reason for this is because the relationship itself isn't cached. The next test reads the football teams players; this relationship is cached.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    FootballTeam team = session.Get<FootballTeam>(teamId);
    int count = team.Players.Count;
}

The first time this piece of code runs, NHibernate has to go to SQLServer for the data. So once again we repeat the same bit of code.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    FootballTeam team = session.Get<FootballTeam>(teamId);
    int count = team.Players.Count;
}

This time NHibernate can get the data from NCache. This is all pretty straight forward so far. The next thing I tried to do was to read a Coach.

const int coachId = 1;

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    Coach coach = session.Get<Coach>(coachId);
}

Nhibernate read coach 1 from NCache. Hang on a minute how did this happen? We haven't read and cached a coach yet! In fact we have. When the football teams coaches were accessed above, NHibernate didn't cache the relationship. In other words when NHibernate is asked to:

NHibernate can't find this in the cache, but it did cache each of the individual coaches because I configured NHibernate to cache all entities. This means that it can find coach 1 in the cache and doesn't need to go to the database for it.

The next thing I tried was adding new entities.

int newPlayerId;

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    using ( var txn = session.BeginTransaction() )
    {
        FootballTeam team = session.Get<FootballTeam>(teamId);

        Player newPlayer = new Player()
        {
            FootballTeam = team,
            Name = "Wayne Rooney",
            Position = PlayerPosition.Striker,
            Injured = false
        };
        session.SaveOrUpdate(newPlayer);
        newPlayerId = newPlayer.Id;

        txn.Commit();
    }
}

The save needs to be wrapped in a transaction so that it gets flushed down to the database. When the transaction is committed an insert statement is output. We then read the football team and access its player list again.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    FootballTeam team = session.Get<FootballTeam>(teamId);
    int count = team.Players.Count;
}

Because NHibernate kept the cache in sync when it added the new player, NHibernate still doesn't need to go to the database for the list of players, it can simply read it from the cache. Just to prove the new player was created properly and that I'm not being returned a stale list, I then read the new player directly.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    Player player = session.Get<Player>(newPlayerId);

    if ( player == null )
        throw new Exception("New player does not exist");
}

The exception doesn't get thrown and again, NHibernate can obtain the data being requested from the cache.

Next I start looking at queries. When I say queries I mean querying the data through the ICriteria interface. To get the most out of second-level cacheing, in my opinion, you need to avoid queries as much as possible. In terms of cacheing, the best you can do is cache the queries. When you do this the data that is stored in the cache is keyed by the query and the parameters therefore if you continually use the same query, but with different parameters, even if you have told NHibernate to cache the results, it will still have to go to the database for the data because the parameters are different each time.

Is this always the case though? What about if we use ICriteria to get all of football team 1's players. We know from above, that we already have this data cached.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<Player>()
               .Add<Player>(p => p.FootballTeam.Id == teamId); // I'm using NHibernate.LamdaExtensions to be able to use lamda's here

    query.List<Player>();
}

No, even though all of football team 1's players are cached, this query hasn't been cached previously so NHibernate will hit the database for the data. So then I repeated the same query but this time told NHibernate to cache the results.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<Player>()
               .Add<Player>(p => p.FootballTeam.Id == teamId)
               .SetCacheable(true);

    query.List<Player>();
}

Nhibernate has to go to the database this time because last time I didn't cache the query.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<Player>()
               .Add<Player>(p => p.FootballTeam.Id == teamId)
               .SetCacheable(true);

    query.List<Player>();
}

The second time the now cached query is called, NHibernate can get the results from the cache rather than having to read the data from the database again. The next test performs the same query again but this time for team 2 rather than 1.

const int team2Id = 2;

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<Player>()
               .Add<Player>(p => p.FootballTeam.Id == team2Id)
               .SetCacheable(true);

    query.List<Player>();
}

As expected, this time NHibernate has to go to the database for the results. I then wanted to make sure that NHibernate could cache the results of a query for more than one set of parameters, so ran the players query yet again, but this time for team 1 again.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<Player>()
               .Add<Player>(p => p.FootballTeam.Id == teamId)
               .SetCacheable(true);

    query.List<Player>();
}

Nhibernate does cache the results for more than one set of parameters and so was able to read the result of this query from the cache again.

Next up I wanted to see what would happen if I read a football team that I already had cached, with a query. I expected this to go to the database because I haven't run this query before. However, what would happen if I then accessed the players list again. Nhibernate should have cached that relationship above and therefore shouldn't have to go to the database again for them.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<FootballTeam>()
               .Add<FootballTeam>(t => t.Id == teamId)
               .SetCacheable(true);

    FootballTeam team = query.UniqueResult<FootballTeam>();
    int count = team.Players.Count;
}

And sure enough, I saw a select for the football team, but NHibernate was clever enough to read the players from NCache. What would be nice would be if NHibernate was clever enough to realize that the query above is the same as doing session.Get(teamId) but on the other hand it's not really too much to ask the programmer to realize that is it?

The last thing to look at was named queries. I had a couple of stored procedures which I used to perform full text searches configured as named queries. More information on doing that can be found here.

const string searchTerm = "10";

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    IQuery query = 
        session.GetNamedQuery("FTPlayerSearchCount")
               .SetParameter("query", searchTerm)
               .SetCacheable(true);

    query.UniqueResult<int>();
}

The first time the named query is called NHibernate has to go to the database to execute the stored procedure. I then called the named query a second time, but this time with a different search term.

const string newSearchTerm = "1010";

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    IQuery query = 
        session.GetNamedQuery("FTPlayerSearchCount")
               .SetParameter("query", newSearchTerm)
               .SetCacheable(true);

    query.UniqueResult<int>();
}

Again NHibernate had to go to SQLServer to execute the stored procedure. If I run the named query a third time, but this time with the same search string that I did the first time...

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    IQuery query = 
        session.GetNamedQuery("FTPlayerSearchCount")
               .SetParameter("query", searchTerm)
               .SetCacheable(true);

    query.UniqueResult<int>();
}

...NHibernate can read the result from NCache.

Finally I wanted to see what would happen if I added a new player to the cache/database and then ran a query to return all of the players, using the same query that I cached earlier.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    using ( var txn = session.BeginTransaction() )
    {
        FootballTeam team = session.Get<FootballTeam>(teamId);

        Player newPlayer = new Player()
        {
            FootballTeam = team,
            Name = "Rio Ferdinand",
            Position = PlayerPosition.CentreHalf,
            Injured = false
        };
        session.SaveOrUpdate(newPlayer);
        newPlayerId = newPlayer.Id;

        txn.Commit();
    }
}

As expeccted I saw an insert statement when the transaction was committed. I then performed my query to return all of the players and added some code to check whether the new player is returned and to double check that the player is created properly. I would expect Nhibernate to get the results from the cache and because it hasn't gone to the database for the data, I would expect the list not to contain the new player. When I call the Get, to check the player was properly created I would expect the new player to be returned successfully.

using ( var session = NHibernateSession.GetDefaultSessionFactory().OpenSession() )
{
    ICriteria query = 
        session.CreateCriteria<Player>()
               .Add<Player>(p => p.FootballTeam.Id == teamId)
               .SetCacheable(true);

    IList<Player> players = query.List<Player>();
    Player newPlayer = players.Where(p => p.Id == newPlayerId).FirstOrDefault();

    if ( newPlayer != null )
        Console.WriteLine("The new player WAS returned after all!!!");

    if ( session.Get<Player>(newPlayerId) == null )
        throw new Exception("New player wasn't created properly");
}

I was completely wrong!!! NHibernate must have detected that the resultset for this query that was previously cached was now out of date and rather than reading the list of players from the cache it went to the database instead! I think we could probably play around with things like the cache mode or maybe my ncache configuration to make this work how I originally expected. I will leave this as an exercise for the interested reader though!