Translate query to NHibernate
- by Rob Walker
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.
