How to get loaded, filtered child collection using NHibernate criteria API
Can the api criteria be used to load a set of parent objects along with a filtered, eagerly loaded set of child objects? I am trying to query for a list of categories and at the same time load products of categories starting with M. The query below shows the results I want but the products are not eagerly loaded, i.e. NHibernate does additional queries when I list a collection of products :
var categoriesWithProducts = session.CreateCriteria<Category>()
.SetFetchMode("Products", FetchMode.Eager)
.CreateCriteria("Products")
.Add(Expression.Like("Name", "M%"))
.List<Category>();
What am I missing here?
a source to share
Be aware that any NHibernate query will result in only one SQL statement.
One solution is to use a join that returns a wide range of results. Depending on the number of products in each category, this may be much less effective. There are also duplicate categories in the results list.
An example of a wide range of results 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",...
To do what you want, you probably need two queries, and therefore two criteria: one for selecting categories and one for retrieving products. If your driver supports it, you can use them along with IMultiCriteria.
a source to share