Criteria API - How to get records based on collection count?

Posted by Cosmo on Stack Overflow See other posts from Stack Overflow or by Cosmo
Published on 2010-05-22T01:50:43Z Indexed on 2010/05/22 3:00 UTC
Read the original article Hit count: 318

Hello Guys!

I have a Question class in ActiveRecord with following fields:

[ActiveRecord("`Question`")]
public class Question : ObcykaniDb<Question> {

    private long id;
    private IList<Question> relatedQuestions;

    [PrimaryKey("`Id`")]
    private long Id {
        get { return this.id; }
        set { this.id = value; }
    }

    [HasAndBelongsToMany(typeof(Question), ColumnRef = "ChildId", ColumnKey = "ParentId", Table = "RelatedQuestion")] 
    private IList<Question> RelatedQuestions {
        get { return this.relatedQuestions; }
        set { this.relatedQuestions = value; }
    }
}

How do I write a DetachedCriteria query to get all Questions that have at least 5 related questions (count) in the RelatedQuestions collection?

For now this gives me strange results:

DetachedCriteria dCriteria = DetachedCriteria.For<Question>()
            .CreateCriteria("RelatedQuestions")
            .SetProjection(Projections.Count("Id"))
            .Add(Restrictions.EqProperty(Projections.Id(), "alias.Id"));

DetachedCriteria dc = DetachedCriteria.For<Question>("alias").Add(Subqueries.Le(5, dCriteria));
IList<Question> results = Question.FindAll(dc);

Any ideas what I'm doing wrong?

© Stack Overflow or respective owner

Related posts about nhibernate

Related posts about activerecord