nhibernate cache problem with linq queries

Question

I am seeing some odd behavior from nhibernate caching and cannot understand the reasoning. I am not able to cache queries when doing select operations like

query.Select(x=>x).ToList() 

but can cache when doing:

var query = session.Linq<Promoter>();
var p = query.ToList();

Both produce the same sql query and should be doign the same thing. The following test explains the problem.

    [Test]
    public void Can_Use_Cache()
    {
       ISessionFactory factory = Storage.Application.Get<ISessionFactory>("SessionFactory");
       // initial hit on the database and load into cache - all fine
        using (var session = factory.OpenSession())
       {
           Console.WriteLine("");
           Console.WriteLine("First Query");
           var query = session.Linq<Promoter>();
           query.QueryOptions.SetCachable(true);
           query.QueryOptions.SetCacheMode(CacheMode.Normal);
           var p = query.ToList();
       }
        // no hit on the database and retrieved from cache as expected - all fine
       using (var session = factory.OpenSession())
       {
           Console.WriteLine("");
           Console.WriteLine("Second Query");
           var query = session.Linq<Promoter>();
           query.QueryOptions.SetCachable(true);
           query.QueryOptions.SetCacheMode(CacheMode.Normal);
           var p = query.ToList();
       }
        // hits the db - should have come from the cache - not working 
       using (var session = factory.OpenSession())
       {
           Console.WriteLine("");
           Console.WriteLine("Third Query");
           var query = session.Linq<Promoter>();
           query.QueryOptions.SetCachable(true);
           query.QueryOptions.SetCacheMode(CacheMode.Normal);
           var p = query.Select(x=>x).ToList();
       }
       // hits the db again - should have come from the cache - again not working
       using (var session = factory.OpenSession())
       {
           Console.WriteLine("");
           Console.WriteLine("Fourth Query");
           var query = session.Linq<Promoter>();
           query.QueryOptions.SetCachable(true);
           query.QueryOptions.SetCacheMode(CacheMode.Normal);
           var p = query.Select(x => x).ToList();
       }
    }

My test results showing the hit on the db for the second query. queries 3 and 4 should not be hitting the db:

2010-02-28 12:05:23,046 INFO Started Logging

First Query
2010-02-28 12:05:23,156 DEBUG SELECT this_.Id as Id11_0_, this_.Version as Version11_0_, this_.Name as Name11_0_ FROM Promoters this_
NHibernate: SELECT this_.Id as Id11_0_, this_.Version as Version11_0_, this_.Name as Name11_0_ FROM Promoters this_

Second Query

Third Query
2010-02-28 12:05:23,315 DEBUG SELECT this_.Id as Id11_0_, this_.Version as Version11_0_, this_.Name as Name11_0_ FROM Promoters this_
NHibernate: SELECT this_.Id as Id11_0_, this_.Version as Version11_0_, this_.Name as Name11_0_ FROM Promoters this_

Fourth Query
2010-02-28 12:05:23,318 DEBUG SELECT this_.Id as Id11_0_, this_.Version as Version11_0_, this_.Name as Name11_0_ FROM Promoters this_
NHibernate: SELECT this_.Id as Id11_0_, this_.Version as Version11_0_, this_.Name as Name11_0_ FROM Promoters this_

The cache is configured using fluent:

SessionFactory = Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008
                          .ConnectionString(ConfigurationService.SqlConnectionString)
                          .ShowSql()
                         .Cache(c => c

                                    .UseQueryCache()
                                    .ProviderClass(typeof(NHibernate.Cache.HashtableCacheProvider).AssemblyQualifiedName))
                          )
            .Mappings(m => m.FluentMappings.AddFromAssemblyOf<EventMap>()
                               .Conventions.Setup(MappingConventions.GetConventions()))
            .ExposeConfiguration(BuildSchema)
            .BuildSessionFactory();

Answer 1

Seems to be a problem with using .Select() as in .Select(x=>x) For some reason the cache is bypassed when using the select. Any other statements work fine like OrderBy(), Where() etc

Sample code below:

using (var session = factory.OpenSession())
           {
               Console.WriteLine("");
               var query = session.Linq<Promoter>();
               query.QueryOptions.SetCachable(true);
               query.QueryOptions.SetCacheMode(CacheMode.Normal);
               var p = query.OrderBy(x => x.Name).ToList();// works fine
               //var p = query.OrderBy(x => x.Name).Select(x=>x).ToList();// will hit the db
           }

Answer 2

Have you tried using transactions?

http://nhprof.com/Learn/Alerts/DoNotUseImplicitTransactions