I get a duplicate association path error in NHibernate criteria while accessing the same table

Question

I have CreateCriteria that adds a join to the same table twice with different aliases:

aCriteria.CreateCriteria("Color", "co").Add(Expression.In("co.ColorId", bikush.Color.Select(x => x.ColorId).ToList())); aCriteria.CreateCriteria("Color","fco").Add(Expression.In("fco.ColorId",bikush.FCColor.Select(x => x.ColorId).ToList())); 

I get the "duplicate association path" error

Here is the SQL I want to generate:

SELECT b.BikushId, c.[Name] AS PlainColor, fc.[Name] AS FancyColor FROM Bikush b INNER JOIN BikushInColor clt ON clt.BikushId = b.BikushId INNER JOIN Color c ON clt.ColorId = c.ColorId INNER JOIN BikushInFCColor bifc ON b.BikushId = bifc.BikushId INNER JOIN Color fc ON bifc.ColorId =fc.ColorId

Anyway using CriteriaApi from Nhibernate?

thanks

Answer 1

If I understand correctly, then you should combine the two calls in one:

  Criteria.CreateCriteria("Color", "co") .Add(Expression.In("co.ColorId", bikush.Color.Select(x => x.ColorId).ToList())); .Add(Expression.In("co.ColorId", bikush.FCColor.Select(x => x.ColorId).ToList())); 

Answer 2

The alias is for the property, not the table name. Try:

 aCriteria.CreateCriteria("Color", "co").Add(Expression.In("co.ColorId", bikush.Color.Select(x => x.ColorId).ToList())); aCriteria.CreateCriteria("FancyColor","fco").Add(Expression.In("fco.ColorId",bikush.FCColor.Select(x => x.ColorId).ToList()));