CreateSqlQuery doesn't retrieve scalar value in NHibernate

Question

Why does codigoUnificacion variable is always null?

public int? GetCodigoUnificacionFamiliar(IList<Pariente> parientes)
        {
            List<string> cedulas = new List<string>();

            parientes.ToList().ForEach(p => cedulas.Add(p.Cedula));

            int? codigoUnificacion = Session
                .CreateSQLQuery(@"SELECT DISTINCT
                                            sa_bec_mtc_doc as Codigo
                                    FROM    sa_bec_matricula
                                            INNER JOIN sa_matricula ON sa_bec_matricula.sa_mtc_num = sa_matricula.sa_mtc_num
                                            INNER JOIN sa_alumno ON sa_matricula.sa_alu_cod = sa_alumno.sa_alu_cod
                                            INNER JOIN sa_periodo ON sa_matricula.sa_per_cod = sa_periodo.sa_per_cod
                                            INNER JOIN sa_tpo_beca ON sa_bec_matricula.sa_tpo_bec_cod = sa_tpo_beca.sa_tpo_bec_cod
                                    WHERE   sa_alu_ced IN (:cedulas)
                                            AND sa_per_abi = 1
                                            AND sa_tpo_bec_gpr = 2")
                .SetParameterList("cedulas", cedulas)
                .UniqueResult() as int?;

            return codigoUnificacion;
        }

Answer 1

Instead of using var, you can define the type you are expecting.

Example

int max = session.CreateSQLQuery(query).UniqueResult<int>();

This way you don't have to try and apply a cast after the fact.

Answer 2

When Execute Queries that Return Scalar Values in NHibernate

This will work!

var max = session.CreateSQLQuery(query).UniqueResult();
decimal s = max != null ? Decimal.Parse(max.ToString()) : -1;

This will return null !!!

decimal? s = session.CreateSQLQuery(query).UniqueResult() as decimal?;

This will not compile

Because only reference or nullable types are accepted

decimal s = session.CreateSQLQuery(query).UniqueResult() as decimal;

TypeConversionException

Exception is thrown from NHibernate

decimal s = (decimal)session.CreateSQLQuery(query).UniqueResult();