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.

Aspic

It looks like you should probably use COUNT instead of SUM. – Calcium

Count doesn't accept conditions, how could I use it here ? Two queries ? – Aspic

If it doesn't accept queries, you could use WHERE to select only the ones you want to count, and then use COUNT. – Calcium

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. – Aspic

One query. Check if value == MyEnum.Two OR value == MyEnum.Three – Calcium

Which you could probably do using SUM. Why have you written them separately, anyway? – Calcium

Can you post the full code ? I'm not sure but I think you didn't get what I asked. – Aspic

Aer you using QueryOver? – Feet

@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 ! – Aspic

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 – Calcium

@Jim, I showed the SQL needed for this kind of results so I guess this should work with NH too. – Aspic

Answer 1

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

Feet

Do you know how this will look like if I use QueryOver instead? – Typewritten

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 – Typewritten

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. – Feet

The problem with QueryOver is that it doesn't support Any in its where clause – Cuspidation

@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 – Feet

Yes I was talking about the latter.Where we can have Any in our Where clause but it's not possible in a QueryOver. – Cuspidation