Querying on Collection with Nhibernate Criteria Api?

Question

I have an "Estate" entity, and this entity has a collection "EstateFeatures"(type:EstateFeature) and EstateFeature has a property "MyFeatureValue".

Note: These are the limited properties for the question. All Entities has an Id and all necesarry etc

Estate

IList<EstateFeature> EstateFeatures;

EstateFeature

FeatureValue MyFeatureValue;

FeatureValue

public virtual long Id;

I am trying to get Real Estates which have the given FeatureValue.Id

DetachedCriteria query = DetachedCriteria.For<Estate>();
Conjunction and = new Conjuction();
foreach (var id in idCollection)
   and.Add(Expression.Eq("MyFeatureValue.Id",id);

query
     .CreateCriteria("EstateFeatures")
     .Add(and);
IList<Estate> estates = query.GetExecutableCriteria(session).List<Estate>();

Nothing returned from this query, am i doing something wrong ?

Thanks

Answer 1

You will need to make sure that you join MyFeatureValue one time for each feature that you want your Estate to have.

One way is to call .CreateAlias for each iteration, give it a unique alias then add expression "aliasX.Id"

foreach (var id in idCollection)
{
   query = query.CreateAlias("MyFeatureValue", "feature" + id)
                .Add(Expression.Eq("feature" + id + ".Id",id);


}

Doesnt really recall how the syntax goes, wrote this out of my head, not sure if you need to redeclare query either :)

However, I think this will get you started.

EDIT: Since a bug in the Criteria API restrain you from associating a collection multiple times using CreateAlias or CreateCriteria, you need to resort to HQL.

http://derek-says.blogspot.com/2008/06/duplicate-association-path-bug-in.html

(Hibernate suffers from the same issue aswell)

select e   
FROM Estate AS e
INNER JOIN e.MyFeatureValue AS fv1
INNER JOIN e.MyFeatureValue AS fv2
WHERE fv1.Id = 3
   AND fv2.Id = 13

you will need to build the HQL dynamically so that your aliases becomes unique (fv1, fv2, fvX ...)