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.

Pseudohermaphrodite

Answer 1

Try casting the Price to nullable decimal...

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

The result is clearly a decimal?

Whereupon

This is almost the solution, please change it to .Sum(invoiceLine => (decimal?)invoiceLine.Price) ?? 0 so I can upvote and accept. – Totemism

Answer 2

The solution marked as answer didn't save the problem for me. Instead of GenericAdoException I got a InvalidOperationException with the following stack.

Proposed solution here Linq: select property collection is the actual solution for me.

Une erreur inattendue de type InvalidOperationException s'est produite sur le serveur
System.InvalidOperationException: Code supposed to be unreachable
   à System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   à System.Linq.Expressions.Compiler.StackSpiller.RewriteUnaryExpression(Expression expr, Stack stack)
   à System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   à System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   à System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   à System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   à System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)
   à System.Linq.Expressions.Expression`1.Compile()
   à NHibernate.Linq.ExpressionToHqlTranslationResults.MergeLambdasAndCompile[TDelegate](IList`1 itemTransformers)
   à NHibernate.Linq.ExpressionToHqlTranslationResults..ctor(HqlTreeNode statement, IList`1 itemTransformers, IList`1 listTransformers, IList`1 postExecuteTransformers, List`1 additionalCriteria)
   à NHibernate.Linq.IntermediateHqlTree.GetTranslation()
   à NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root)
   à NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)
   à NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   à NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   à NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   à NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   à NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   à NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
   à NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery)
   à NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
   à NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
   à System.Linq.Queryable.Sum[TSource](IQueryable`1 source, Expression`1 selector)
   à Secib.Server.Services.ReglementService.GetRegleTtc(FactureView facture) dans x:\Sources\Next\Alpha\SecibNext\Secib.Server\Services\ReglementService.cs:ligne 425
   à Secib.Server.Services.ReglementService.DoesMontantSoldeFacture(FactureView facture, Decimal montantImpute) dans x:\Sources\Next\Alpha\SecibNext\Secib.Server\Services\ReglementService.cs:ligne 391
   à Secib.Server.Services.ReglementService.DispatcheMontantImpute(Reglement reglement, ReglementFactureCompactDto factureDto) dans x:\Sources\Next\Alpha\SecibNext\Secib.Server\Services\ReglementService.cs:ligne 236
   à Secib.Server.Services.ReglementService.SaveReglement(Reglement reglement, IList`1 factures) dans x:\Sources\Next\Alpha\SecibNext\Secib.Server\Services\ReglementService.cs:ligne 197
   à Secib.Server.Controllers.ReglementController.<SaveReglement>z__OriginalMethod() dans x:\Sources\Next\Alpha\SecibNext\Secib.Server\Controllers\ReglementController.cs:ligne 141
   à Secib.Server.Controllers.ReglementController.<SaveReglement>c__Binding.Invoke(Object& instance, Arguments arguments, Object aspectArgs) dans :ligne 0
   à PostSharp.Aspects.Internals.MethodInterceptionArgsImpl`1.Proceed()
   à Secib.Server.Aspects.TransactionAspect.OnInvoke(MethodInterceptionArgs args) dans x:\Sources\Next\Alpha\SecibNext\Secib.Server\Aspects\TransactionAspect.cs:ligne 32

Segmental

What inner exception where you getting and how did your query/entities look like? Unless something changed in the way it works, I'm pretty sure the accepted answer works. – Totemism

That SelectMany second snipped of code is also making 2 requests to the DB instead of 1 (could be written using futures but it would still be less efficient). – Totemism

InnerException is null. The query is very simple and very similar to yours : return Session.Query<ReglementFacture>() .Where(x => x.Facture == facture) .Sum(x => (decimal?)x.MontantRegle ?? 0); Where Facture is referenced by ReglementFacture (many to one) and MontantRegle is decimal (and the column is not nullable). NH v 4.0.0.4000 – Segmental

You need to call ?? on the entire query, not inside the sum lambda: return Session.Query<ReglementFacture>() .Where(x => x.Facture == facture) .Sum(x => (decimal?)x.MontantRegle) ?? 0; That is because Sum will return null when there are no elements in the collection. And you need to cast to decimal? because Sum can return null for empty collection and the return type is inferred from what type you return. – Totemism

Answer 3

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

Lietuva

It throws exception not because there are null prices, but because database returns null when summing an empty collection. So this isn't gonna solve OP's issue. – Frodeen