nhibernate join two entity and return another entity

Question

I have a master entity Trnx like that:

 public class MasterTrnx
 {
    private int? _AccountId;
    private string _Place;
    private DateTime _ProcessTime;
    private int _TrnxId;
    private decimal? _PaymentValue;
  }

And the master's child entity like that:

  public class MasterTrnxDetail
  {
    private MasterTrnx _MasterTrnx;
    private decimal _MasterPaymentValue;
    private decimal _PaymentValue;
    private int _Xid;
  }

One MasterTrnx entity has one more than MasterTrnxDetail child.

 using (ISession session = base.GetSession())
        {
            try
            {
                tx = session.BeginTransaction();

                listOfMasterTrnxDetail = session.QueryOver<MasterTrnxDetail>()
                    .JoinQueryOver(d => (IEnumerable<MasterTrnx>)d.Trnx)
                    .List();

                tx.Commit();
            }
            catch (Exception e)
            {
                if (tx != null)
                {
                    tx.Rollback();
                }
                throw e;
            }
            finally
            {
                session.Close();
            }

            return listOfMasterTrnxDetail;
        }

This code block is working. For example I have a master entity and it has 3 three masterdetail. this code gives me 3 records. But I want one master record and total of details' MasterPaymentValues . How can I do that? Also I want the method returns another entity like that:

public class Trnx
{
    public decimal? PaymentValue { get; set; }
    public DateTime ProcessTime { get; set; }
    public string TrnxName { get; set; }
    public decimal? TotalMasterPaymentValue { get; set; }
}

Thanks for help.

Firo · Answer 1 · 2015-05-06T10:59:44.080

your class model seems a bit odd, so i can only guess

MasterTrnx mastertx = null;

var subquery = QueryOver.Of<MasterTrnxDetail>()
    .Where(detail => detail.MasterTrnx = mastertx)
    .Select(Projections.Sum<MasterTrnxDetail>(d => d.PaymentValue));


Trnx dto = null;

transactions = session.QueryOver(() => mastertx)
    .SelectList(list => list
        .Select(t => t.ProcessTime).WithAlias(() => dto.ProcessTime)
        .Select(t => t.Place).WithAlias(() => dto.TrnxName)
        .Select(Projections.Subquery(subquery)).WithAlias(() => dto.TotalMasterPaymentValue)
    )
    .TransformUsing(Transformers.AliasToBean<Trnx>())
    .List<Trnx>();

nhibernate join two entity and return another entity

1 Answers1