I have a problem with a SQL query results in NHibernate 3.1.0.4000 (I'm using MS SQL Server 2005 database). I have two tables: Adverts and Investments, which are mapped to entities: Advert and Investment. Adverts are connected with Investments via InvestmentId column (and a foreign key).

要救我的查询结果我创建了以下内部类：

To save results of my query I created a following inner class:

    class InvestmentWithAdvertsCount
    {
        public Investment inv { get; set; }

        public int cnt { get; set; }
    }

和查询是如下：

var investementsWithAdvertCounts = _session.CreateSQLQuery(
                            "select {inv.*}, (select count(1) from Adverts where InvestmentId = inv.Id) cnt from Investments inv")
                            .AddScalar("cnt", NHibernateUtil.Int32)
                            .AddEntity("inv", typeof(Investment))
                            .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InvestmentWithAdvertsCount)))
                            .List<InvestmentWithAdvertsCount>();

当我运行此查询我收到 InvestmentWithAdvertsCount 实例具有正确填写 CNT 属性集合，但在 INV 属性设置为空。我花了一些时间挖掘到NHibernate的来源，它似乎是的 INV 的别名，以某种方式从查询丢失和NHibernate甚至没有尝试，填补了 INV 属性。如果我删除 .SetResultTransformer 部分我收到阵列的列表（类型目标[2] ）与第一要素设置为投资实体，并设置其相应的广告数第二个元素。你能告诉我如何改变这个查询，使 InvestmentWithAdvertsCount 填写正确？或者，也许有一种方法可以重写此查询到QueryOver，标准或HQL（保持生成的SQL code高效）？

When I run this query I receive a collection of InvestmentWithAdvertsCount instances that have a correctly filled cnt property, but the inv property is set to null. I spent some time digging into the NHibernate source and it seems that the inv alias is somehow lost from the query and NHibernate does not even try to fill the inv property. If I remove the .SetResultTransformer part I receive a list of arrays (of type object[2]) with first element set to Investment entity and second element set to its corresponding adverts count. Could you please tell me how to change this query to make the InvestmentWithAdvertsCount filled correctly? Or maybe there is a way to rewrite this query to QueryOver, Criteria or HQL (keeping the generated SQL code efficient)?

我会很感激在这个问题上的任何帮助。谢谢

I would be really greatful for any help on this issue. Thanks

推荐答案

下面是一个干净的方式做到这一点，结果非常简单的LINQ处理的对象：

Here's a clean way to do it, processing the results with very simple LINQ to objects:

session.CreateSQLQuery(
    "select {inv.*}, (select count(1) ... inv")
    .AddScalar("cnt", NHibernateUtil.Int32)
    .AddEntity("inv", typeof(Investment))
    .List<object[]>()
    .Select(x => new InvestmentWithAdvertsCount {inv = x[0], cnt = x[1]})
    .ToList();