Using NHibernate to report a table with two sums -

i have 3 tables: people, purchases, payments 1-to-many relationships between people , purchases, , people , payments.

i want generate report of people showing sum of purchases , payments.

i can generate report people showing sum of payments or purchases, vis:

var query =    detachedcriteria.for<people>("people")   .createalias("payments", "paymentsmade");  query.setprojection(projections.projectionlist()   .add(projections.groupproperty("id"), "id")   .add(projections.sum("paymentsmade.amount"), "totalpayments") 

can in single query in nhibernate? either using criteria api (preferred) or hql.

try this:

var query = @"select                 (select sum(pu.amount) purchase pu pu.people.id = ppl.id),                 (select sum(pa.amount) payments pa pa.people.id = ppl.id)                people ppl";  var results = session                 .createquery(query)                 .list(); 

or perhaps using ad-hoc mapping:

public class bulkreport {     public double purchases { get; set; }     public double payments  { get; set; } }  var results = session                 .createquery(query)                 .setresulttransformer(transformers.aliastobean(typeof(bulkreport)))                 .list<bulkreport>(); 

another option using multiqueries.