NHibernate CreateSQLQuery

Question

Im trying to get some data with NH CreateSQLQuery method like

IList<Logistic> LCollection = sess.CreateSQLQuery(@"select * from some_schema.logistic")
                                           .SetResultTransformer(Transformers.AliasToBean(typeof(Logistic)))
                                           .List<Logistic>();

logistic class is

public class Logistic
{
    public virtual long? l_id { get; set; }
    public virtual long? carrier_id { get; set; }
    ...
}

mapping

public class LogisticMap : ClassMap<Logistic>
{
    public LogisticMap()
    {
        Table("some_chema.logistic");
        Id(x => x.l_id).GeneratedBy.Sequence("some_chema.logistic_sq");
        Map(x => x.carrier_id);
        ...
    }
}

but i have the error

The type System.Decimal can not be assigned to a property of type System.Nullable`1[System.Int64] setter of MyNamespase.Logistic.l_id

any idea what may be wrong?

Answer 1

An AliasToBean transformer is used when you want to retrieve lightweight DTO's instead of entities. (For instance, if you have an overview-screen, which displays only some essential information of each entity, then it is better to use a DTO and create a query in NHibernate which uses an AliasToBean transformer so that NH knows that it shouldn't retrieve the complete entities).

If you want to retrieve entities using a SQL query, you'll have to do it like this:

var query = sess.CreateSQLQuery(@"select {l.*} from some_schema.logistic as l");

query.AddEntity ("l", typeof(Logistic));

return query.List<Logistic>();                                  

But, I wonder why you'd want to use a native SQL query in this case ? Why not use HQL, ICriteria or QueryOver ?