NHibernate.QueryException: duplicate link path

Question

The code snippet below describes what I want to do with queries, but it explodes with the above error.

There is 1 to many relationships between Buildings and AttributeValues, and my goal is to find all Buildings with AttributeValue "Large" and AttributeValue of "Blue" .

  var attributeValueAlias1 = new AttributeValue(); var attributeValueAlias2 = new AttributeValue(); var result = repository .CreateCriteriaFor<Buildings>() .Join(o=>o.AttributeValues, ()=> attributeValueAlias1) .Join(o=>o.AttributeValues, ()=> attributeValueAlias2) .Add(() => attributeValueAlias1.Value == "Large") .Add(() => attributeValueAlias2.Value == "Blue") .List(); 

There are a few posts, but there is not a single solution to the problem described here here and here .

I can make the add test be added using criteria.GetCriteriaByAlias(alias) before adding an alias, so duplication alias error does not occur, but then there is only one connection, and this leads to an SQL where clause, which will never be true

 SELECT * FROM Buildings this_ inner join AttributeValues attributev1_ on this_.Id = attributev1_.BuildingId WHERE attributev1_.Value = 'Large' and attributev1_.Value = 'Blue' 

This is not a complicated or unusual request, so I would be surprised if it does not work.

Answer 1

You should not use the connection for this, as you probably guessed from the exception. You should use two subqueries to check if the values ​​"Blue" and "Big" are in the collection. I'm not very good at ICriteria, but the resulting SQL should look like this, and I think this is possible in ICriteria (using Subqueries.PropertyIn ).

 select * from Buildings b inner join AttributeValues av on b.Id = av.BuildingId where av.Id in (select Id from AttributeValues av2 where av2.BuildingId=b.BuildingId and av2.Value="Large") and av.Id in (select Id from AttributeValues av3 where av3.BuildingId=b.BuildingId and av3.Value="Blue")