Nhibernate criteria query inserts an extra order by expression when using JoinType.LeftOuterJoin and Projections
- by Aaron Palmer
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.
