I am getting a "duplicate association path" error in the NHibernate Criteria when accessing the same table twice.

Question

I have a 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'm getting the error "duplicate association path"

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

Is there anyway around this using the CriteriaApi of Nhibernate?

Thanks

Answer 1

If I understand correctly, then you should merge thies 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()));