NHibernate - Eager Fetch, Order By and a strange behavior with ICriteria

Print Content | More

Working on Dexter I encountered a very strange NHibernate behavior (I’m using NHIbernate 2.1.2 GA and this is very close to be a bug in my opinion) while trying to apply the eager fetch mode to a child collection whose mapping contains an order-by attribute applied.

The scenario is this: you have 2 classes called: ‘Item’ and ‘Tag’, Item holds a collection of tags, the relation is mapped as inverse and we have an order-by clause applied to the collection mapping (the tags are ordered by name); we want to fetch a list of Item classes along with all its tags (to optimize things) and we want the result to be sorted by some field of the main Item class.

The problem is: performing the query using ICriteria and setting the eager fetch mode on the tags collection you don’t get the results with the order you expect (the one you specified in the ICriteria query).

To reproduce the behavior let’s consider the following mapping:


	
		
	
	
	
	
		
		
	



	
		
	
	
	


 

We can build-up a very simple to create some data in the database:

public void CreateTestData()
{
	string[] tagsName = new[] { "zapp", "crunch", "bang", "sbanf" };
	using (ISession session = NHelper.OpenSession())
	using (ITransaction tx = session.BeginTransaction())
	{
		// create some items
		for (int i = 0; i < 10; i++)
		{
			Item itm = new Item() { Name = "Item" + i, Sort = i };
			session.SaveOrUpdate(itm);
			
			Tag t = new Tag();
			t.Name = tagsName[i / 3];
			t.Item = itm;
			itm.Tags.Add(t);
			session.SaveOrUpdate(t);
		}
		tx.Commit();
	}
}

 

I can now write a very simple ICriteria to retrieve all the Items (alongside with their tags) ordering the result by the ‘Sort’ field; I expect to see 10 records in my result ordered from 0 to 10 in ascending order:

...
ICriteria myCriteria = session.CreateCriteria();

myCriteria
	.SetResultTransformer(Transformers.DistinctRootEntity)
	.SetFetchMode("Tags", FetchMode.Eager)
	.AddOrder(new Order("Sort", true));

result = myCriteria.List();
...

What I get instead are 10 records...but ordered by Tag name FIRST (which is the order I asked for in the mapping), I really expected this order by to be applied AFTER what I imposed while building the ICriteria.

To confirm the problem we can take a look at the query NHibernate generates and execute that in our SQL environment:

NHibernateEagerOrderBy1
As you can see this isn’t really what I expected, to double check I also tried to get the data using an HQL query:

IQuery query = session.CreateQuery("from Item itm left join fetch itm.Tags order by itm.Sort");

result = query.List();

But this time everything is fine...and I see the result ordered by ‘Sort’ first and after by tag’s Name:

NHibernateEagerOrderBy2

In the end: when using eager fetching with collection mapped with order-by clauses, always perform some tests to verify if what you obtain satisfies the order you imposed on your queries. If you get strange behaviors using ICriteria, try switching to HQL queries as they seems to act better according to you needs.

 

In the following solution you can find a test project to reproduce the problem:

 



Eager, Fetch, Hql, ICriteria, Nhibernate

0 comments

Related Post

All fields are required and you must provide valid data in order to be able to comment on this post.


(will not be published)
(es: http://www.mysite.com)


  1. #1 da http://blog.cwa.me.uk/2010/07/14/the-morning-brew-642/

    The Morning Brew - Chris Alcock &raquo; The Morning Brew #642