How to transform SQL query in DTO with nullable properties?

Question

Having a DTO like this:

public class CustomerDTO
{
     public int Id{get; set;}
     public int? Reference {get; set;}
}

How can I get it from

var q =_session.CreateSQLQuery("SELECT Id, Reference FROM customers")

If I use

q.SetResultTransformer(Transformers.AliasToBean<CustomerDTO>)

I get the following exception:

NHibernate.PropertyAccessException : The type System.Int32 can not be assigned to a property of type System.Nullable`1[System.Int32] setter of Customer.Reference

Answer 1

Try this:

var customers = _session.CreateSQLQuery("SELECT Id, Reference FROM customers")
    .AddScalar("Id", NHibernateUtil.Int32)
    .AddScalar("Reference", NHibernateUtil.Int32)
    .SetResultTransformer(Transformers.AliasToBean<CustomerDTO>())
    .List<CustomerDTO>();

Reference here.

Answer 2

We had to map a nullable enum and were getting:

Object of type 'System.Int32' cannot be converted to type 'System.Nullable`1[ExampleEnumType]'.

The fix was to use:

.AddScalar("EnumTypeSqlAlias", NHibernateUtil.Enum(typeof(ExampleEnumType)))