2nd level cache problem with join query

Question

I want to use Second level Cache for my query with eager loading(query is below wrote in 3 different ways, i use query cache). I have standard one to many association. I set entity cache for parent, child, and association between parent and class. And the 2nd level cache doesn't work because i got exceptions.

I wrote my query in 3 different way:
Criteria:

 session.CreateCriteria<DictionaryMaster>().SetFetchMode("DictionaryItems", FetchMode.Eager)
                .SetResultTransformer(new DistinctRootEntityResultTransformer())
                .SetCacheable(true).SetCacheMode(CacheMode.Normal)
                    .List<DictionaryMaster>().ToList();

When I invoked this query i got exception "Unable to perform find[SQL: SQL not available]" I think that the problem exists because I use DistinctRootEntityResultTransformer transform. I will start create my custom transform class and I hope that will work.

Query over:

 session.QueryOver<DictionaryMaster>().Fetch(x => x.DictionaryItems).Eager
                    .TransformUsing(new DistinctRootEntityResultTransformer())
                    .Cacheable().CacheMode(CacheMode.Normal)
                    .List<DictionaryMaster>().ToList();

Exception is the same as in Criteria.

Linq:

session.Query<DictionaryMaster>().Fetch(x => x.DictionaryItems).Cacheable().CacheMode(CacheMode.Normal).ToList();

Here error depends from the version of nhibernate in 3.1 a got this error https://nhibernate.jira.com/browse/NH-2587?page=com.atlassian.jira.plugin.system.issuetabpanels%3Achangehistory-tabpanel but in 3.2 version i got this: https://nhibernate.jira.com/browse/NH-2856

Thanks in advance

Answer 1

sJHony I have found solution, but for me is more like workaround. Therefore if you know any other solution give me sign.
I utilize QueryOver without any transformation, the fault of this solution is that the query return elements equal amount of childs. Next I retrieve not multiplied list in memory using distinct.
This solution is ok, but when we add one more Fetch for query, then collection in object also be multiplied, that is why i modified collection type from IList(Bag) to ISet(Set).

Code looks like:

var queryCacheResult =
                session.QueryOver<DictionaryMaster>()
                .Fetch(x => x.DictionaryItems).Eager
                    .Cacheable().CacheMode(CacheMode.Normal)
                    .List<DictionaryMaster>().ToList();

            return queryCacheResult.Distinct(new KeyEqualityComparer<DictionaryMaster>(x => x.Code)).ToList();