Translate query to NHibernate

Question

I am trying to learn NHibernate, and am having difficulty translating a SQL query into one using the criteria API.

The data model has tables: Part (Id, Name, ...), Order (Id, PartId, Qty), Shipment (Id, PartId, Qty)

For all the parts I want to find the total quantity ordered and the total quantity shipped. In SQL I have:

select shipment.part_id, sum(shipment.quantity), sum(order.quantity)  
  from shipment cross join order
  on order.part_id = shipment.part_id
  group by shipment.part_id

Alternatively:

select id, 
   (select sum(quantity) from shipment where part_id = part.id), 
   (select sum(quantity) from order where part_id = part.id)
  from part

But the latter query takes over twice as long to execute.

Any suggestions on how to create these queries in (fluent) NHibernate? I have all the tables mapped and loading/saving/etc the entities works fine.

Answer 1

Well - I don't really get the whole idea of your model - but maybe this criteria will work for you. Otherwise check the NHibernate documentation for criteria APIs Section Projections, aggregation and grouping.

List results = session.CreateCriteria(typeof(Shipment))
    .CreateAlias("Order", order)
    .SetProjection (Projections.ProjectionList()
        .Add (Projections.GroupProperty("id"), "id)
        .Add (Projections.Sum ("Quantity"), "shipmentQuantity)
        .Add (Projections.Sum ("order.Quantity"), "orderQuantity)
    ).List();