Example taken from
NHibernate Bugreport #381:
session.CreateCriteria(typeof(Contact))
.Add(Expression.Eq("Name", "Bob")
.SetFetchMode("Emails", FetchMode.Join)
.CreateCriteria("Emails")
.Add(Expression.Eq("EmailAddress", "Bob@hotmail.com"))
.List();
The resulting SQL will include a Join to Emails as expected, the resultset returned by the database is OK, but within the object model Contact.Emails is not going to be populated with data. Which means once Contact.Emails is being accessed in code, lazy loading occurs, which probably was not the coder's intention. This is not the case when
CreateCriteria("Emails")
.Add(Expression.Eq("EmailAddress", "Bob@hotmail.com"))is omitted.
The bug report was closed without fix, but contained a comment that "According to Hibernate guys this is correct behavior" and a link to
Hibernate bug report #918. To me that sounds plausible. Think about it: A Hibernate object child collection usually contains ALL children, that's expected behavior. So the child criteria might narrow down the set of parent objects, but not the set of children-per-parent. And there is just no way you could have both in one and the same SQL join - narrow the resultset in regard to the parent rows, but still deliver all their children.
Anyway, this can be worked around by phrasing the same query in HQL, or by starting with the child Criteria and applying an outer Join (which of course is somewhat semantically different):
session.CreateCriteria(typeof(Contact))
.Add(Expression.Eq("Name", "Bob")
.CreateCriteria("Emails", JoinType.LeftOuterJoin)
.Add(Expression.Eq("EmailAddress", "Bob@hotmail.com"))
.List();
Or, as a third option (and my personal favorite): Place that whole Criteria tree (let's call it "SubCriteria") into a subquery as part of another Criteria (which we'll call "MainCriteria"). Both SubCriteria and MainCriteria look pretty similar and consist of the same joins, but MainCriteria does the fetching (FetchMode.Join), while SubCriteria does the narrowing (Expression.Eq). Bot Criterias are correlated by parent key equality.
On a related topic, eagerly joining several child associations has the drawback that the resultset consists of a cartesian product over all children - lots of rows with duplicate data. Let's say there are three child associations A, B and C with 10 rows each for a given parent row, joining all three associations will blast up the resultset to 1 x 10 x 10 x 10 = 1000 rows, when only 1 + 10 + 10 + 10 = 31 rows would be needed.
And while those duplicates will only lead to duplicate references in the object model (and not to duplicate instances), and even those duplicate references can be eliminated again by using Maps or Sets for child collections, these Joins impose a severe performance and memory drawback on the database resp. ADO.NET level.
Of course one could simply issue N single select statements, one for each table, with equaivalent where-clauses. But that implies N database roundtrips as well. Not so good.
The means to avoid this are Hibernate Criteria- and HQL-MultiQueries. Gabriel Schenker has posted a really
nice article on MultiQueries with NHibernate.
Posts
