0

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"); } 

1 Answer 1

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")) 
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.