NHibernate QueryOver CASE何时对列值进行计算 [英] NHibernate QueryOver CASE WHEN calculate on column value

问题描述

我一直在尝试在NHibernate QueryOver中执行以下T-SQL，但没有成功:

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

我试图总结所有的音量，但是对于MyValue=1应该为正值，否则为负值.到目前为止，我得到了:

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();

但是您可以想象NHibernate不知道列Volume * -1，那么如何在CASE中进行此计算呢?

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

推荐答案

我认为这应该可以解决问题:

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[]>();

通常，QueryOver在执行算术时非常糟糕.据我所知，您必须使用VarArgsSQLFunction来构建乘法表达式.

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.

这将生成以下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

请注意，您需要在此处使用与自定义DTO配对的结果转换器，或使用.List<object[]>，这会将结果集转换为object[]的List，List中的每个项目都是结果行.您不能只使用.List()，因为NHibernate希望选择出整个MyTable行，而您在这里没有这样做.

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[]>();

这篇关于NHibernate QueryOver CASE何时对列值进行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！