NHibernate QueryOver CASE WHEN calculate on column value

Question

I have been trying to do the following T-SQL in NHibernate QueryOver, but have not succeeded:

SELECT Id, SUM(CASE MyValue WHEN 1 THEN Volume ELSE Volume * -1 END)
FROM MyTable
GROUP BY Id

I am trying to sum up all Volume, but for MyValue=1 should be positive values otherwise negative values. So far I got:

 var result = this.Session.QueryOver<MyTable>()
    .Select(Projections.Group<MyTable>(x => x.Id),
    Projections.Conditional(Restrictions.Eq(Projections.Property<MyTable>(x
        => x.MyValue), '1'),
    Projections.Property<MyTable>(x => x.Volume),
    Projections.Property<MyTable>(x => x.Volume * -1)))
    .List();

But as you can imagine NHibernate don't know the column Volume * -1 , so how do I do this calculation in my CASE?

Answer 1

I think this should do the trick:

session.QueryOver<MyTable>()
    .Select(
        Projections.Group<MyTable>(x => x.Id),
        Projections.Sum(
            Projections.Conditional(
                Restrictions.Eq(
                    Projections.Property<MyTable>(x => x.MyValue), 1),
                Projections.Property<MyTable>(x => x.Volume),
                Projections.SqlFunction(
                    new VarArgsSQLFunction("(", "*", ")"),
                    NHibernateUtil.Int32,
                    Projections.Property<MyTable>(x => x.Volume),
                    Projections.Constant(-1)))))
    .List<object[]>();

As a rule, QueryOver is pretty terrible at doing arithmetic. As far as I know, you have to use VarArgsSQLFunction to build the multiplication expression.

This generates the following SQL:

SELECT
    this_.Id as y0_,
    sum((
        case when this_.MyValue = 1 
        then this_.Volume else (this_.Volume*-1) end
    )) as y1_
FROM        
    MyTable this_     
GROUP BY        
    this_.Id

Note that you need to use a result transformer paired with a custom DTO here, or use .List<object[]>, which will transform the result set into a List of object[], each item in the List being a result row. You can't just use .List() because NHibernate expects to be selecting out entire MyTable rows, which you aren't doing here.

You're probably thinking that this is pretty ugly, and I'd agree. You could clean it up a little by refactoring projections into their own variables:

IProjection multiplicationProjection = 
    Projections.SqlFunction(
        new VarArgsSQLFunction("(", "*", ")"),
        NHibernateUtil.Int32,
        Projections.Property<MyTable>(t => t.Volume),
        Projections.Constant(-1));

IProjection conditionalProjection = 
    Projections.Conditional(
        Restrictions.Eq(
            Projections.Property<MyTable>(t => t.MyValue), 1),
        Projections.Property<MyTable>(t => t.Volume),
        multiplicationProjection);

session.QueryOver<MyTable>()
    .SelectList(list => list
        .SelectGroup(t => t.Id)
        .Select(Projections.Sum(conditionalProjection)))
    .List<object[]>();