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.
