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.

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

• HQL query examples.
• LINQ for NHibernate examples.