Creating NHibernate Queryover to get the sum of two field products with a group of

Question

I have two tables Invoiceand InvoiceLineItem. This table InvoiceLineItemcontains:

Id
InvoiceId
Quantity
UnitPrice 

the columns. I want to create an NHibernate QueryOver statement to group invoice items by InvoiceIdand get the amount of the product UnitPriceandQuantity

SQL looks like this.

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

I used Projections.Sum, but I'm not sure how we can multiply two columns by it (if this is the right way).

Answer 1

This doesn't seem to be a great way to do this. By creating this answer , you can 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.

This will create SQL that looks like this:

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