Nhibernate criteria query inserts an extra order by expression when using JoinType.LeftOuterJoin and Projections
Posted
by
Aaron Palmer
on Stack Overflow
See other posts from Stack Overflow
or by Aaron Palmer
Published on 2009-04-29T13:41:12Z
Indexed on
2012/09/23
21:38 UTC
Read the original article
Hit count: 330
Why would this nhibernate criteria query produce the sql query below?
return Session.CreateCriteria(typeof(FundingCategory), "fc")
.CreateCriteria("FundingPrograms", "fp")
.CreateCriteria("Projects", "p", JoinType.LeftOuterJoin)
.Add(Restrictions.Disjunction()
.Add(Restrictions.Eq("fp.Recipient.Id", recipientId))
.Add(Restrictions.Eq("p.Recipient.Id", recipientId))
)
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("fc.Name"), "fcn")
.Add(Projections.Sum("fp.ObligatedAmount"), "fpo")
.Add(Projections.Sum("p.ObligatedAmount"), "po")
)
.AddOrder(Order.Desc("fpo"))
.AddOrder(Order.Desc("po"))
.AddOrder(Order.Asc("fcn"))
.List<object[]>();
SELECT this_.Name as y0_,
sum(fp1_.ObligatedAmount) as y1_,
sum(p2_.ObligatedAmount) as y2_
FROM fundingCategories this_
inner join fundingPrograms fp1_
on this_.fundingCategoryId = fp1_.fundingCategoryId
left outer join projects p2_
on fp1_.fundingProgramId = p2_.fundingProgramId
WHERE (fp1_.recipientId = 6 /* @p0 */
or p2_.recipientId = 6 /* @p1 */)
GROUP BY this_.Name
ORDER BY p2_.name asc,
y1_ desc,
y2_ desc,
y0_ asc
It is incorrectly putting the p2_name asc into the ORDER BY statement, and causing it to crash. This only happens when I use JoinType.LeftOuterJoin on my Projects criteria. Is this a known nhibernate bug? I'm using nhibernate 2.0.1.4000. Thanks for any insight.
© Stack Overflow or respective owner