NHibernate Named Query Not Returning All Fields

I have the following Named Query defined in Forecast.hbm.xml:

SELECT 
    c.ForecastedYear,
    s.Description,
    Sum(c.NumTrees) AS NumTrees
FROM 
    EcoForecasts AS f INNER JOIN 
    (EcoForecastCohorts AS c INNER JOIN EcoStrata AS s ON s.StrataKey = c.StratumKey) 
    ON f.ForecastKey = c.ForecastKey
WHERE 
    f.ForecastKey = :guid
GROUP BY 
    c.ForecastedYear,
    c.StratumKey,
    s.Description
ORDER BY 
    s.Description,
    c.ForecastedYear;

I call the query in the C# file NumTreesByStrataReport.cs like this:

IList<Result> results = _input.GetNamedQuery("NumTreesByStrata")
                              .SetGuid("guid", _iSess.ForecastKey.Value)
                              .List<object[]>()
                              .Select(result => new Result {
                                  Year     = Convert.ToInt16(result[0]),
                                  Strata   = Convert.ToString(result[1]),
                                  NumTrees = Convert.ToInt64(result[2])
                              })
                              .ToList();

return results;

However, this code throws an IndexOutOfRangeException because the .List<object[]>() part is returning a list of object arrays with only 3 elements. The s.Description field is not being returned.

If I run the SQL query in my database engine (Microsoft Access), then all 3 columns are returned. Why is NHibernate not able to do what the database can do and return all the selected fields?

Answers


I would suggest using a result to bean transformer instead, it will be much cleaner code.

return _session.GetNamedQuery("NumTreesByStrata")
    .SetGuid("guid", forecastKeyValue)
    .SetResultTransformer(Transformers.AliasToBean(typeof(Result)))
    .List<Result>();

Thank you all for your help. It turns out the issue was actually with my mapping file:

    <sql-query name="NumTreesByStrata">
        <return-scalar column="ForecastedYear" type="Int16"/>
        <return-scalar column="NumTrees" type="Int64"/>
        <![CDATA[
          SELECT c.ForecastedYear,
          ...
        ]]>
    </sql-query>

I added the missing <return-scalar column="Description" type="string"/> element to make it work.

I would suggest using a result to bean transformer instead, it will be much cleaner code.

Thanks for the suggestion Alexey, I didn't know you could do that!


Need Your Help

How do you change background colors of different containers and columns?

html twitter-bootstrap background-color

Ok, so I'm brand new here (and to programming) and I'm not even sure If the questions I'm going to ask is worded correctly or makes sense, but here it goes.

How do I change mysql settings so that it is default UTF-8 for everything?

mysql database encoding utf-8

I am getting "ASCII encoding" errors when I insert into my database because I did a fresh install of the MYSQL.