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 ?

Mark Vincze · Accepted Answer · 2015-06-11 14:30:57Z

9

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>();
}

edited Jun 11, 2015 at 14:30

Mark Vincze

8,16110 gold badges50 silver badges86 bronze badges

answered Nov 27, 2010 at 12:49

sh_kamalh

3,9014 gold badges42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MadSeb Over a year ago

actually the SetResultTransformer thing does not work for me ... I get an error message saying : Error - NHibernate.PropertyNotFoundException: Could not find a setter for property 'Manufacturer' in class Product .............. ( This is somehow true as I don't have a property Manufacturer in the class product; the property is called ManufacturerName ; the nhibernate xml mapping specifies that ManufacturerName prop. maps to the Manufacturer column in the table )

sh_kamalh Over a year ago

Instead of select * you can specify the columns you want from the product table. This way SetResultTransformer will work.

Ms01 Over a year ago

@sh_kamalh I specified all columns and I still got the same error

madth3 · Accepted Answer · 2012-12-06 01:21:54Z

4

Yes you can. Check out Entity Queries using Native SQL : http://codewut.de/content/using-native-sql-nhibernate

edited Dec 6, 2012 at 1:21

madth3

7,33712 gold badges53 silver badges74 bronze badges

answered Nov 27, 2010 at 12:05

basarat

278k61 gold badges475 silver badges528 bronze badges

2 Comments

Liedman Over a year ago

The link does not work any longer. "This content is no longer available on Knol."

basarat Over a year ago

Here's another. codewut.de/content/using-native-sql-nhibernate Or just google "Entity Queries using Native SQL NHibernate" :)

Collectives™ on Stack Overflow

NHibernate: Get object by SQL Query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related