Bound FetchMany in Linq to NHibernate

Question

I am using FetchMany for some of my queries and the NHibernate profiler gives me the following error:

WARN:
firstResult/maxResults specified with collection fetch; applying in memory!

I guess this is because the fetch is unbound. Is there a solution to this?

Answer 1

This problem arises because using FetchMany will bring the whole result set to memory and then Take the specified subset (something inefficient and potentially dangerous).

Apparently there is no way to limit the subset before fetching when using FetchMany.

The solution is to use either a JoinQueryOver or a JoinAlias (differences of the two have been discussed in other SO questions)

So insted of doing

Session.QueryOver<Product>().FetchMany(p=>p.Images).Take(5)

which produces the warning in the question, we do

Image image = null;
Session.QueryOver<Product>().Left.JoinQueryOver(x => x.Images, () => image).Take(5)

which produces a SELECT TOP (5) query