This problem exists in Hibernate itself as well and, contrary to some comments I've seen in the bug tracker, I believe it is a bug in the Criteria API and not in HQL.
A trivial example
I have a Store type representing a shop which has a collection, Products, containing Product types the shop stocks e.g. "Golf Balls", "Bananas", "Hats" etc. I want to get all the stores who stock both Golf Balls and Hats.
In HQL this would be :
SELECT s FROM Store AS s INNER JOIN s.Products AS prod1 INNER JOIN s.Products AS prod2 WHERE prod1.Type = 'Golf Balls' AND prod2.Type = 'Hats'
...pretty straight forward and works fine.
In Criteria API this would be:
IList stores = sess.CreateCriteria(typeof(Store)) .CreateAlias("Products", "prod1") .CreateAlias("Products", "prod2") .Add( Expression.EqProperty("prod1.Type", "Golf Balls") ) .Add( Expression.EqProperty("prod2.Type", "Hats") ) .List();
...again straight forward and seems logical but this produces an error...
NHibernate.QueryException: duplicate association path Products
As I said, it seems like a pretty solid candidate for a bug and it's odd considering surely the meaning of CreateAlias is that I want to use the same association more than once so need to alias it to different labels.
Unfortunately there's no way to get around this issue if you need distinct association joins like the above example and looking at the NHibernate code it doesn't seem like an easy fix. If, however, you can apply your criterion or sorts to the same alias then there is a workaround.
If we take a look at this handy NHibernate API reference we see that the two implementing classes for ICriteria are NHibernate.Impl.CriteriaImpl and NHibernate.Impl.CriteriaImpl.Subcriteria.
CriteriaImpl is the root criteria you get calling CreateCriteria on ISession and Subcriteria you get with every call to CreateCrteria or CreateAlias on ICriteria.
First you need to retrieve the root CriteriaImpl for your working ICriteria. Your working ICriteria may be the root but if it isn't you need to recurse up through the Parent property until you reach the CriteriaImpl object.
CriteriaImpl has an IterateSubcriteria method which returns an IList of all its Subcriteria descendants. You can loop through this list checking the Parent and Path properties of each item. The Parent because the value of Path is relative and you're only interested in what will be sibling Subcriteria to the one you're about to add.
If you find a match you can retrieve its alias from the Alias property, otherwise you can add a new alias to your working ICriteria.
Update - Jan 2014
It seems this bug is still not fixed in NHibernate (or Hibernate for that matter) and that it may also affect the LINQ provider. The relevant issues links are:
- HHH-879: Enable joining the same association twice with Criteria
This is the original Hibernate bug which was ported to the .NET version.
- NH-2016: Duplicate Association Path when creating multiple aliases
This is the same issue against NHibernate.
- NHLQ-43: Duplicate alias QueryException thrown when executing a modified linq query
Although this is a issue reported against the now deplrecated NHLQ project it's unclear whether the issue is fixed.
- NH-3472: Joining to association twice using criteria causes: NHibernate.QueryException: duplicate association path
This is a duplicate of the NHibernate bug
I'm very tempted to have a go at fixing this myself given there still seem to be a few people struggling with it. Will post another update if I get anywhere.