NHibernate: Get object by SQL Query

Question

Is it possible to do something like this in NHibernate?

Product GetSpecificProduct()
{
    return session.CreateSQLQuery("SELECT * FROM Products WHERE price =
        $500").UniqueResult<Product>();
}

When I try to run this code I get:

System.InvalidCastException: Unable to cast object of type 'System.Object[]' to type Product.

Or do I have to use the NHibernate query language ?

Answer 1

If you have to use CreateSqlQuery, you can use the following:

Product GetSpecificProduct()
{
   ISQLQuery query = session.CreateSQLQuery("SELECT * FROM Products WHERE price = $500");
   Product p = query.SetResultTransformer(Transformers.AliasToBean<Product>()).UniqueResult<Product>();
}

I suggest you better use ICriteria as in:

Product GetSpecificProduct()
{
   ICriteria c = session.CreateCriteria();
   c.Add(Expression.Eq("Price", 500));
   return c.UniqueResult<Product>();
}