怎么办与NHibernate有条件的总和？ [英] How to do a conditional Sum with Nhibernate?

问题描述

我试图做到这一点SQL代码相当于

  SELECT 
 ID 
 SUM （CASE WHEN myProperty的= 2 THEN 1 ELSE 0 END）为nbRowWithValueOf2，
 SUM（CASE WHEN myProperty的= 3，那么1 ELSE 0 END）作为nbRowWithValueOf3 
从foo 
 GROUP BY ID 
  

与NHibernate。

到目前为止，我试过

 可查询=可查询
。选择（
 Projections.Group<富>（C => c.ID ），
 Projections.Sum<富>（C => c.myProperty == MyEnum.Two？1：0）
 Projections.Sum<富>（C => c.myProperty == ？MyEnum.Three 1：0）
）
  

但是，这给了我下面的错误：

无法从IIF（（转换（c.myProperty）= 2），1,0）

$ b成员确定$ b

你有什么想法

编辑1：我可以得到2查询的结果，但我想这样做只有1查询

编辑2：我使用QueryOver这里

解决方案

我觉得这应该工作（queryOver语法）：

  queryover = queryover 
。选择（
 Projections.Group<富>（C => c.ID），
 Projections.Sum（
 Projections.Conditional（
 Restrictions.Where<富>（F => f.myProperty == MyEnum.Two），
 Projections.Constant（1），
 Projections.Constant（0））），
 Projections.Sum（
 Projections.Conditional（
 Restrictions.Where&所述;富>中（f => ; f.myProperty == MyEnum.Three）
 Projections.Constant（1），
 Projections.Constant（0））））; 
  

这应该给你的SQL语句：

<前类=郎-SQL prettyprint-覆盖> SELECT this_.ID为y0_，
SUM（（案例
时this_.myProperty = 2 / * @ * P0 /再1 / * @ * P1 /
，否则0 / * @ * P2 /
端））为y1_，
SUM（（案例
时this_.myProperty = 3 / * @ P3 * /然后1 / * @ * P4 /
，否则0 / * @ * P5 /
端））为y2_
从[美孚] THIS_
GROUP BY this_.ID


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.

解决方案

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

这篇关于怎么办与NHibernate有条件的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！