NHibernate: SQL query results mapping

Question

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; }
    }

and the query is as follows:

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

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

Answer 1

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