problem with creating a query using criteria in nhibernate

Question

I want to reproduce the following query using criteria:

select a.ID as article, count(c.ID) as commentsCount
from Comments c 
left outer join Articles a on a.ID=c.ArticleID 
where a.ID in (2,10)
group by a.ID

I wrote it, but somewhere I must have an error, because the result is different than I expected.

The criteria:

ICriteria criteria = Session.CreateCriteria<Comment>("c")  
                .CreateCriteria("Article", "a", 
                                NHibernate.SqlCommand.JoinType.LeftOuterJoin)
                .Add(Restrictions.In("a.ID", articleIDs))
                .SetProjection(Projections.Property("a.ID"))
                .SetProjection(Projections.Count("c.ID"))
                .SetProjection(Projections.GroupProperty("a.ID"));

NHibernate generates the following sql using this criteria:

SELECT   a1_.ID as y0_
FROM     Comments this_
         left outer join Articles a1_
           on this_.ArticleID = a1_.ID
WHERE    a1_.ID in (2 /* @p0 */,10 /* @p1 */)
GROUP BY a1_.ID

And the mapping:

public class Comment : EntityBase<int>
{
        public virtual Article Article { set; get; }

}

public CommentMap()
        {
            Id(x => x.ID).GeneratedBy.Identity();        
            References(x => x.Article).Not.Nullable()
                                      .LazyLoad().Cascade
                                      .SaveUpdate().Column("ArticleID");
        }

Diego Mijelshon · Accepted Answer · 2010-10-06 19:07:43Z

3

Every time you use SetProjection you replace the previous one.

You need to use:

.SetProjection(Projections.Property("a.ID"),
               Projections.Count("c.ID"),
               Projections.GroupProperty("a.ID"))

answered Oct 6, 2010 at 19:07

Diego Mijelshon

52.7k16 gold badges119 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

problem with creating a query using criteria in nhibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related