Creating NHibernate Queryover to get sum of two field product with group by

Question

I have two tables Invoice and InvoiceLineItem. This InvoiceLineItem table contains:

Id
InvoiceId
Quantity
UnitPrice 

columns. I want to create NHibernate QueryOver statement to Group the invoice line items by InvoiceId and get the sum of the product of UnitPrice and Quantity

SQL is like this.

SELECT InvoiceId, SUM(Quantity*UnitPrice) AS Total
  FROM InvoiceLineItem
GROUP BY InvoiceId

I used Projections.Sum but I am not sure how we can multiply two columns inside that (if that is the correct way).

Answer 1

It doesn't look like there's a great way to do this. Building off of this answer, you could use VarArgsSQLFunction:

InvoiceLineItem lineItemAlias = null;

session.QueryOver<InvoiceLineItem>(() => lineItemAlias)
    .SelectList(list => list
        .Select(Projections.Sum(
            Projections.SqlFunction(new VarArgsSQLFunction("(", "*", ")"),
            NHibernateUtil.Double,
            Projections.Property(() => lineItemAlias.Quantity),
            Projections.Property(() => lineItemAlias.UnitPrice))))
        .SelectGroup(() => lineItemAlias.Invoice.Id)
   // TransformUsing, List<>, etc.

That would generate SQL that looks like this:

SELECT
    sum((this_.Quantity*this_.UnitPrice)) as y0_,
    this_.InvoiceId as y1_
FROM
    InvoiceLineItem this_
GROUP BY
    this_.InvoiceId