Tutorial :NHibernate CreateSqlQuery and addEntity



Question:

The hibernate manual says this:

String sql = "SELECT ID as {c.id}, NAME as {c.name}, " +

   "BIRTHDATE as {c.birthDate}, MOTHER_ID as {c.mother}, {mother.*} " +      "FROM CAT_LOG c, CAT_LOG m WHERE {c.mother} = c.ID";  

List loggedCats = sess.createSQLQuery(sql)

  .addEntity("cat", Cat.class)      .addEntity("mother", Cat.class).list()  

Now, what I have is basically the same. I am return two of the same type per row. I am doing a select something like this:

SELECT {ctrl1.*}, {ctrl2.*} FROM tableA AS A                                                  LEFT JOIN tableB AS ctrl1 ON (A.controlID = ctrl1.controlID AND ctrl1.controlOptionType = ? AND ctrl1.controlOptionValue = ?)                                                  LEFT JOIN tableB AS ctrl2 ON (A.controlID = ctrl2.controlID AND ctrl2.controlOptionType = ? AND ctrl2.controlOptionValue = ?)  

And then I addEntity("ctrl1", typeof(mycontrolclass) and addEntity("ctrl1", typeof(mycontrolclass)

Which seems exactly the same to me as their example. But I get this exception: "Could not execute query" and the inner exception is "Could not find specified column in results". If I copy the sql in the exception(to which it has added "AS ctrl1_1_3_3_" etc) it works fine.

Thanks.


Solution:1

What exactly are you trying to do? I believe you might not need using either of them.

// Using HQL:  var motherId = 25;  var hql = "select c.birthDate, c.mother from Cat c where c.mother.Id = :motherId";  var result = Session.CreateQuery(hql)                      .SetParameter("motherId", motherId)                      .ToList();    // Using NHibernate.LINQ:  var result = (from cat in Session.Linq<Cat>()               where cat.Mother.Id == motherId               select new { cat.birthDate, cat.mother }).ToList();  


Solution:2

I dealt with your problem just for studying purposes, because you will surely have found a solution in the meanwhile, but the problem should not lie in the query (which is ok), but in some mapping inconsistency or somewhere else (perhaps Database).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »