C# – NHibernate CreateSQLQuery

cnhibernate

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?

Best Answer

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 ?

Related Topic