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: 303

Filed under:
|
|

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

Related posts about nhibernate

Related posts about order