How to do a conditional Sum with Nhibernate?

Question

I'm trying to do the equivalent of this SQL Code

SELECT 
ID
SUM(CASE WHEN myProperty = 2 THEN 1 ELSE 0 END) as nbRowWithValueOf2,
SUM(CASE WHEN myProperty = 3 THEN 1 ELSE 0 END) as nbRowWithValueOf3
FROM Foo
GROUP BY ID

With Nhibernate.

So far I tried

queryable = queryable
    .Select(
        Projections.Group<Foo>(c => c.ID),
        Projections.Sum<Foo>(c => c.myProperty == MyEnum.Two ? 1 : 0)
        Projections.Sum<Foo>(c => c.myProperty == MyEnum.Three ? 1 : 0)
)

But this gives me the following error:

Could not determine member from IIF((Convert(c.myProperty) = 2), 1, 0)

Do you have any idea ?

EDIT 1 : I can get the result with 2 queries but I want to do this in only 1 query.

EDIT 2 : I'm using QueryOver here.

It looks like you should probably use `COUNT` instead of `SUM`. — yoozer8, Mar 19 '12 at 16:55
Count doesn't accept conditions, how could I use it here ? Two queries ? — remi bourgarel, Mar 19 '12 at 16:57
If it doesn't accept queries, you could use `WHERE` to select only the ones you want to count, and then use `COUNT`. — yoozer8, Mar 19 '12 at 17:00
So that's Two queries, one for the rows with the value MyEnum.Two and one for the rows with the value MyEnum.Three. That's definitely working, but I prefer to do this in only one query. — remi bourgarel, Mar 19 '12 at 17:02
One query. Check if value == MyEnum.Two `OR` value == MyEnum.Three — yoozer8, Mar 19 '12 at 17:04
Which you could probably do using `SUM`. Why have you written them separately, anyway? — yoozer8, Mar 19 '12 at 17:05
Can you post the full code ? I'm not sure but I think you didn't get what I asked. — remi bourgarel, Mar 19 '12 at 17:05
@Jim, because I want them separately. I want to know how many records have a value of two and how many have the value of Three ! — remi bourgarel, Mar 19 '12 at 17:06
Alright I think I see what you are asking now. You would like to display one column that shows the number of elements with value==2 and one column with the number of elements with value==3. Since those are two different cases producing separate results, you will need 2 queries — yoozer8, Mar 19 '12 at 17:09
@Jim, I showed the SQL needed for this kind of results so I guess this should work with NH too. — remi bourgarel, Mar 20 '12 at 08:09

score 15 · Accepted Answer · edited Aug 21 '13 at 11:57

15

I think this should work (QueryOver syntax):

queryover = queryover
    .Select(
        Projections.Group<Foo>(c => c.ID),
        Projections.Sum(
            Projections.Conditional(
                Restrictions.Where<Foo>(f => f.myProperty == MyEnum.Two),
                Projections.Constant(1),
                Projections.Constant(0))),
        Projections.Sum(
            Projections.Conditional(
                Restrictions.Where<Foo>(f => f.myProperty == MyEnum.Three),
                Projections.Constant(1),
                Projections.Constant(0))));

Which should give you the following SQL:

SELECT this_.ID as y0_,
       sum((case
              when this_.myProperty = 2 /* @p0 */ then 1 /* @p1 */
              else 0 /* @p2 */
            end))               as y1_,
       sum((case
              when this_.myProperty = 3 /* @p3 */ then 1 /* @p4 */
              else 0 /* @p5 */
            end))               as y2_
FROM   [Foo] this_
GROUP  BY this_.ID

edited Aug 21 '13 at 11:57

Oskar Berggren

5,583
1
19
36

answered Mar 19 '12 at 17:11

Andrew Whitaker

124,656
32
289
307

Do you know how this will look like if I use QueryOver instead? – Alexey Zimarev Jun 26 '12 at 14:45
I mean using SelectGroup instead of Projections.Group, SelectSum instead of Projections.Sum and so on, so QueryOver with inline syntax to be precise with terms – Alexey Zimarev Jun 27 '12 at 08:15
The problem is that there's no inline syntax for `Projections.Conditional`. You have to drop back into criteria for the `case when x then y end`. – Andrew Whitaker Jun 27 '12 at 12:24
The problem with QueryOver is that it doesn't support Any in its where clause – Beatles1692 Apr 27 '15 at 15:17
@Beatles1692: What do you mean exactly? Do you mean generating an `any` clause in the generated SQL? If so, this is possible with the `WithSubquery.WhereSome`. If you mean using LINQ's `.Any` extension method, no that probably won't work because LINQ syntax isn't the same as the QueryOver API – Andrew Whitaker Apr 27 '15 at 15:21
Yes I was talking about the latter.Where we can have Any in our Where clause but it's not possible in a QueryOver. – Beatles1692 Apr 27 '15 at 21:44

How to do a conditional Sum with Nhibernate?

1 Answers1

Linked