Maybe you could try this: var result = session.Query<Account>().Where( a => ((int?)a.SubAccounts. AsQueryable().Sum( sub => sub.Balance)).GetValueOrDefault(0) < 1000 ) . ToList();
2013/9/11 Piotr Stecko <[email protected]> > Hi , > > I have a following issue with LINQ and aggregates : > > > - let's assume I have an entity with one-to-many collection (for > example account with list of sub-accounts) > - on the sub-account level I have balance (i.e. whatever numeric value) > - I want to get all accounts that have less than some specific total > balance > > So the query representing last point would look like : > > var result = session.Query<Account>().Where( a => a.SubAccounts. > AsQueryable().Sum( sub => sub.Balance) < 1000 ) . ToList(); > > Now i noticed that the query part responsible for getting Sum is generated > like this : > > (select cast( sum( subAccount .Balance) as INT) > from // ... boring > where // boring ) < 1000 > > which is fine except for the case when *SubAccounts *collection is empty > - for empty collections the SQL query will compare NULL < 1000 which is > false. > Now since I would expect the *Sum()* from empty collection to be equal to > 0 (same as what is returned for *IEnumerable)* I wanted to control this > behavior and there are few options that I see > > 1) include additional condition inside LINQ query to handle empty > collections > > For the example above it is enough to write > > var result = session.Query<Account>().Where( a => a.SubAccounts. > AsQueryable().Sum( sub => sub.Balance) < 1000 || !a.SubAccounts.Any() ) . > ToList(); > > But this requires both developer attention to remeber about handling > empty collections and also generates much more complicated query so I would > like to avoid that as much as possible. > > 2) add custom extension , like "MySum" and map it from LINQ to HQL > > I have first added *Coalesce()* extension and mapped it with > implementation of *BaseHqlGeneratorForMethod *so that I'm able to write : > > var result = session.Query<Account>().Where( a => a.SubAccounts. > AsQueryable().Sum( sub => sub.Balance)*.**Coalesce(0)* < 1000 ) . ToList > (); > > which is far better but still requires combination of two methods so I > thought about another extension , something like "MySum" > > public static class QueryExtensions > { > public static int MySum<TEntity>(this IList<TEntity> collection, > Func< > TEntity, int> selector) > where TEntity : Entity > { > return collection.AsQueryable().Sum(selector).Coalesce(0); > } > } > > for which I wrote mapper : > > public class MySumGenerator : BaseHqlGeneratorForMethod > { > public MySumGenerator() > { > SupportedMethods = new[] { ReflectionHelper. > GetMethodDefinition(() => QueryExtensions.MySum(null,(Func<Connection,int > >)null)) }; > } > > public override HqlTreeNode BuildHql(MethodInfo method, > ExpressiontargetObject > , ReadOnlyCollection<Expression> arguments, > HqlTreeBuilder treeBuilder, > IHqlExpressionVisitor visitor) > { > var selector = visitor.Visit(arguments[1]).AsExpression(); > > var sum = treeBuilder.Sum(selector); > return treeBuilder.Coalesce(sum,treeBuilder.Constant(0)); > } > } > > > and the use case would look like : > > var result = session.Query<Account>().Where( a => a.SubAccounts. > AsQueryable().MySum( sub => sub.Balance) < 1000 ) . ToList(); > > But this throws exception > about*"NHibernate.Hql.Ast.ANTLR.InvalidPathException : Invalid path: > 'sub.Balance'"* which is not very surprising since I'm never using the > collection in the generator class but frankly I have no idea how to use it > correctly - does anyone know to write custom mapper for aggregate functions > ? > > > 3) modify the nHibernate way of creating Sum() syntax > inside HqlGeneratorExpressionTreeVisitor > > It is enough to change VisitNhSum method as presented below (italic > parts are new) > > protected HqlTreeNode VisitNhSum(NhSumExpression expression) > { > return > * ** _hqlTreeBuilder.Coalesce(* > _hqlTreeBuilder.Cast(_hqlTreeBuilder.Sum(VisitExpression( > expression.Expression).AsExpression()), expression.Type) > * **,_hqlTreeBuilder.Constant(0))* > ; > } > > > so that null's will be converted to 0 and I'm happy and query is still > simple and so on. But since this is not a standard behavior i wondered if > you know the reason for this ? Why NULL values in Sum are not handled by > default ? > > > Any comments ? :) > > Regards, > PS > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/groups/opt_out.
