Projections with Restrictions.Discjunction

Projections with Restrictions.Discjunction() or Restrictions.Or() will produce WHERE clause instead of HAVING clause. Which will result in error (The WHERE clause cannot refer to aggregate expressions).

Example:

.CreateCriteria(typeof(SalesOrderLine), "SOL")
.CreateCriteria("DeliveryOrderLines", "DOL", JoinType.LeftOuterJoin)
.SetProjection(
    Projections.GroupProperty("SOL.Key").As("SalesOrderLineId"),
    Projections.GroupProperty("SOL.Item"),
    Projections.GroupProperty("SOL.Description"),
    Projections.GroupProperty("SOL.UnitPrice"),
    Projections.GroupPr开发者_高级运维operty("SOL.Quantity"),
    Projections.GroupProperty("SOL.DiscountPercentage"),
    Projections.GroupProperty("SOL.SalesOrder"))
.Add(Restrictions.Or(
    Restrictions.IsNull(Projections.Sum("DOL.Quantity")),
    Restrictions.GtProperty("SOL.Quantity", Projections.Sum("DOL.Quantity")))), 
.List();

SQL Result:

SELECT this_.SalesOrderLineId as y0_, this_.Item as y1_, this_.Description as y2_, this_.UnitPrice as y3_, this_.Quantity as y4_, this_.DiscountPercentage as y5_, this_.SalesOrderId as y6_ FROM SalesOrderLine this_ left outer join DeliveryOrderLine dol1_ on this_.SalesOrderLineId=dol1_.SalesOrderLineId 
WHERE (sum(dol1_.Quantity) is null or this_.Quantity > sum(dol1_.Quantity))
GROUP BY this_.SalesOrderLineId, this_.Item, this_.Description, this_.UnitPrice, this_.Quantity, this_.DiscountPercentage, this_.SalesOrderId

Am i doing it wrong ? or is this a bug in Nhibernate 3.1 ?

Thanks in advance :).

Last time I checked the HAVING clause isn't supported by the Criteria API. You'll need to use HQL, which does support HAVING.

To use with Criteria API you'll have to modify your query to this:

SELECT this_.SalesOrderLineId as y0_, this_.Item as y1_, this_.Description as y2_, this_.UnitPrice as y3_, this_.Quantity as y4_, this_.DiscountPercentage as y5_, this_.SalesOrderId as y6_ FROM SalesOrderLine this_ left outer join DeliveryOrderLine dol1_ on this_.SalesOrderLineId=dol1_.SalesOrderLineId 
WHERE (select sum(Quantity) from DeliveryOrderLine) is null or (select sum(Quantity) from > SalesOrderLine ) > (select sum(Quantity) from DeliveryOrderLine)
GROUP BY this_.SalesOrderLineId, this_.Item, this_.Description, this_.UnitPrice, this_.Quantity, this_.DiscountPercentage, this_.SalesOrderId

The solution that I've given will have some performance hit because of multiple sum calculations, but it'll get the job done.

You could also try this, but I'm not sure if it'll run correctly:

SELECT this_.SalesOrderLineId as y0_, this_.Item as y1_, this_.Description as y2_, this_.UnitPrice as y3_, this_.Quantity as y4_, this_.DiscountPercentage as y5_, this_.SalesOrderId as y6_, SUM(dol1_Quantity) as sum1, SUM(this_.Quantity) as sum2 from SalesOrderLine this_ left outer join DeliveryOrderLine dol1_ on this_.SalesOrderLineId=dol1_.SalesOrderLineId 
WHERE sum1 is null or sum1 > sum2
GROUP BY this_.SalesOrderLineId, this_.Item, this_.Description, this_.UnitPrice, this_.Quantity, this_.DiscountPercentage, this_.SalesOrderId

Hope this helped!