How to achieve an eagerly loaded, filtered child collection with the NHibernate criteria API

Question

Is it possible to use the criteria api to load a set of parent objects along with a filtered, eagerly loaded set of child objects? I'm trying to query a list of categories and at the same time load the categories products that start with the letter M. The query below gives me the results I want but the Products are not eagerly loaded, that is NHibernate performs additional queries when I enumerate the Product collection:

 var categoriesWithProducts = session.CreateCriteria<Category>()
    .SetFetchMode("Products", FetchMode.Eager)
    .CreateCriteria("Products")
    .Add(Expression.Like("Name", "M%"))
    .List<Category>();

What am I missing here?

Answer 1

The thing to remember is that any NHibernate query will only result in a single SQL statement.

One solution is to use a join, which would return a wide result set. Depending on the number of products in each category, this may end up being a lot less efficient. You will also have duplicate Categories in your result list.

Example wide result set resulting from a join.

CategoryId,CategoryName,OtherCategoryColumns...,ProductId,ProductName,OtherProductStuff
1,"White stuff",...,1,"Refridgerator",...
1,"White stuff",...,2,"White paint",...
1,"White stuff",...,3,"Milk",...

In order to do what you want, you probably need two queries, and therefore two criteria: one to fetch the categories, and one to fetch the products. If your driver supports it, you can batch these together with IMultiCriteria.