Linq to NHibernate: sum of sums

Question

Having NHibernate entities:

Company, Invoice and InvoiceLine which has a decimal property Price.

A Company has a collection of type Invoice, and an Invoice has a collection of type InvoiceLine.

How can I obtain the sum of all prices that belong to invoice lines which belong to invoices of a certain company specified by id?

I tried to write the query like this:

session
    .Query<InvoiceLine>()
    .Where(invoiceLine => invoiceLine.Invoice.Company.Id == companyId)
    .Sum(invoiceLine => invoiceLine.Price);

but it throws an exception:

NHibernate.Exceptions.GenericADOException
"Could not execute query[SQL: SQL not available]"

   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)
   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters)
   at NHibernate.Impl.ExpressionQueryImpl.List()
   at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery)
   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Sum[TSource](IQueryable`1 source, Expression`1 selector)

inner exception:

System.ArgumentNullException
"Value cannot be null.\r\nParameter name: item"

   at System.ThrowHelper.IfNullAndNullsAreIllegalThenThrow[T](Object value, ExceptionArgument argName)
   at System.Collections.Generic.List`1.System.Collections.IList.Add(Object item)
   at NHibernate.Util.ArrayHelper.<>c__DisplayClass2.<AddAll>b__0()
   at NHibernate.Util.ArrayHelper.AddAll(IList to, IList from)
   at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)

This might have something to do with summing empty collections but I'm not sure how to fix it.

Answer 1

Try casting the Price to nullable decimal...

.Sum(invoiceLine => (decimal?)invoiceLine.Price) ?? 0;

The result is clearly a decimal?