database - 如何使用 NHibernate 的 QueryOver 做到这一点？

Question

在我的数据库中有这三个表，等等

TRANSLATION 具有指向 UNIT 的不可空外键和指向 ASSIGNMENT 的可为空外键。 UNIT 可能分配了更多TRANSLATIONS。

我想执行一个返回具有以下结构的结果的查询:

Language | Assignment | First SUM | Second SUM | Third SUM
==========================================================
    1    |     2      |    456    |    126     |     0                      
    1    |     3      |   5361    |   1367     |    89
   ...   |    ...     |    ...    |    ...     |   ...

结果将按TRANSLATION.fk_language 和TRANSLATION.fk_assignment 分组。我的主要问题是填写剩余的列:

First SUM = SUM(unit.word_count)
Second SUM = SUM(unit.word_count) WHERE translation.status = 1
Third SUM = SUM(unit.word_count) WHERE translation.status = 2

即使我将自己限制在结果中的 First SUM 列，我也无法让它工作:

var result = session.QueryOver<Translation>()
                                .JoinQueryOver<Unit>(x => x.Unit)
                                .Select(Projections.Group<Translation>(x => x.Language),
                                        Projections.Group<Translation>(x => x.Assignment),                                            
                                        Projections.Sum<Unit>(x => x.WordCount))
                                .List<object>()
                                .ToList();

这失败了

could not resolve property: WordCount of: Entities.Translation

非常感谢您的任何建议。

编辑:这是我的映射:

public class TranslationMap : ClassMap<Translation>
{
    public TranslationMap()
    {
        Table("\"TRANSLATION\"");
        LazyLoad();            
        Id(x => x.Id, "id").GeneratedBy.HiLo("hilo", "hilo_translation", "200");
        Map(x => x.Status).Column("status");
        References<Language>(x => x.Language, "fk_id_language").Not.Nullable().Cascade.None();
        References<Unit>(x => x.Unit, "fk_id_translation_unit").Cascade.None();
        References<Assignment>(x => x.Assignment, "fk_id_translator_assignment").Nullable().Cascade.None();
        DynamicUpdate();
    }
}

public class UnitMap: ClassMap<Unit>
{
    public UnitMap()
    {
        Table("\"UNIT\"");
        LazyLoad();            
        Id(x => x.Id, "id").GeneratedBy.HiLo("hilo", "hilo_translation_unit", "200");            
        Map(x => x.Text).Column("text");            
        Map(x => x.WordCount).Column("word_count");            
        HasMany(x => x.Translations).Inverse().KeyColumn("fk_id_translation_unit").Cascade.None();           
    }
}

public class AssignmentMap : ClassMap<Assignment>
{
    public AssignmentMap()
    {
        Table("\"TRANSLATOR_ASSIGNMENT\"");
        LazyLoad();            
        Id(x => x.Id, "id").GeneratedBy.HiLo("hilo", "hilo_translator_assignment", "50");           
    }
}

Answer 1

您是否映射了 Unit 实体的 WordCount 属性？ (你有没有这样的hbm.xml)

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
  <class name="Unit">
    <id name="Id" column="Id" type="Int32" >
      <generator class="native" />
    </id>
    <property name="WordCount" colum="word_count"/>
  </class>
</hibernate-mapping>