CODEWITHSUNDEEP
c#hibernatenhibernateormcriteria
user610217
user610217

Reputation:

(N)Hibernate Criteria: summing multiple distinct columns

None of the similarly worded questions on SO seem to match, and googling pretty much points to SO for this, so let's try this:

I have a JournalEntry entity class that looks like this:

public partial class JournalEntry
{
    public virtual Guid JournalEntryId { get; set; }

    public virtual Account Account { get; set; }

    public virtual decimal DebitAmount { get; set; }

    public virtual decimal CreditAmount { get; set; }

    [NotNull]
    public virtual DateTime EffectiveDate { get; set; }

    [NotNull]
    public virtual DateTime PostingDate { get; set; }

    public virtual UserProfile PostedBy { get; set; }

    [FullTextIndexed]
    public virtual string Notes { get; set; }

    public virtual Amortization Amortization { get; set; }

    public virtual ExpenseCategories ExpenseCategory { get; set; }

    [Index]
    public virtual bool IsClosed { get; set; }
}

I also have a simple class for holding transaction summaries like so:

public class JournalEntrySummary
{
    public decimal Credits { get; set; }
    public decimal Debits { get; set; }
}

What I would like to do is write a Criteria query that will return the sums of both the Credits property and the Debits property. IOW, I would like something vaguely shaped like this SQL query:

select
    sum(creditamount) as Credits,
    sum(debitamount) as Debits
from 
    journalentries
where
    ...

... and have that populate my JournalEntrySummary object. I've seen lots of examples of how to do one column, and even some examples of adding the two columns together, but no examples of collecting the two distinct summaries and dumping them into a non-domain object.

Is this possible? How would I do it?

Upvotes: 1

Views: 1573

Answers (1)

Vasea
Vasea

Reputation: 5333

Take a look at criteria queries documentation http://nhibernate.info/doc/nh/en/index.html#querycriteria-projection, there are some examples that will answer you question.

For your example you should try this

// using NHibernate.Criterion;
// using NHibernate.Transform;

session.CreateCriteria<JournalEntry>()
   .SetProjection(
       Projections.Sum<JournalEntry>(x => x.DebitAmount).As("Debits"),
       Projections.Sum<JournalEntry>(x => x.CreditAmount).As("Credits"),
       // you can use other aggregates
       // Projections.RowCount(),
       // Projections.Max<JournalEntry>(x => x.EffectiveDate)
    )
    .SetResultTransformer(Transformers.AliasToBean<JournalEntrySummary>())
    .UniqueResult<JournalEntrySummary>();

Upvotes: 2

Related Questions