Tuesday, May 3, 2011

Nhibernate criteria query inserts an extra order by expression when using JoinType.LeftOuterJoin and Projections

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.

From stackoverflow

0 comments:

Post a Comment