SELECT – NHIBERNATE Queryover Case WHEN Calculation Value

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

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

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

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

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

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

session.QueryOver()
 .SelectList(list => list
 .SelectGroup( t => t.Id)
 .Select(Projections.Sum(conditionalProjection)))
 .List();

I have been trying to execute the following in NHibernate QueryOver T-SQL, but did not succeed:

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

I tried to summarize all the volume, but for MyValue=1 it should be positive or negative. So far I got:

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

But you can imagine that NHibernate doesn’t know the column * *, so how to use it in my CASE To perform this calculation?

I think this should solve the problem:

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

As a rule, QueryOver is very bad at doing arithmetic. As far as I know , You must use VarArgsSQLFunction to construct the multiplication expression.

This will generate the following SQL:

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

Please note that you need to use a result converter paired with a custom DTO here, or use .List< object []>, it converts the result set into a list of objects [], each item in the List is a result row. You can’t just use .List() because NHibernate wants to select the entire MyTable row that you didn’t do here.

You may think this is ugly, I agree. You can clean it up by refactoring the projection into your own variables:

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

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

 session.QueryOver()
 .SelectList(list => list
 .SelectGroup(t => t.Id)
 .Select(Projections.Sum(conditionalProjection)))
 .Li st();