Create SqlProjection with alias of joined table

Question

I have this situation:

public class AnswerSet {
public virtual IList<Answer> Answers {set; get;}
}

session.CreateCriteria<AnswerSet>()
        .CreateAlias( "Answers", "a" )
        .SetProjection( Projections.ProjectionList()
          .Add( Projections.GroupProperty( "TopicId" ), "TopicId" )
          .Add( Projections.GroupProperty( "TopicName" ), "TopicName" )
          .Add( Projections.SqlProjection( "count (case **{a}**.numerical_answer
when 1 then 1 when -1 then 1 else null end) as YesAnswers",
         new[] { "YesAnswers" }, new IType[] { NHibernateUtil.Int32 } ), "YesAnswers" )

How can I specify the child collection alias? {a}.numerical_answer is not working and {alias} refers to the AnswerSet.

The SQL equivalent is

select
      [as].topic_id as TopicId
      , [as].topic_name as TopicName
      , count (case [a].numerical_answer
               when 1 then 1
               when -1 then 1
               else null
               end) as YesAnswers
from answer_set [as] join answer [a] on [a].answer_set_id = [as].id

Thank you,
Daniel

Thagard

Answer 1

If numerical_answer does not appear anywhere else in your query, you could just skip the table name, e.g.

"count (case numerical_answer when 1 then 1 when -1 then 1 else null end) as YesAnswers"

Ziegfeld

Doing so is a disaster waiting to happen. This could work today, but when later a new column is added to the database, it may break this query without anyone noticing. Look here: web.archive.org/web/20160417115940/http://sqlblog.com/blogs/… – Redcap

A valid concern. As of today I would probably solve this by using QueryOver and calculating the desired result in code instead of having so many magic strings in the first place. – Ziegfeld

Answer 2

I know this is late, but hope it helps anyone. I figured it out from the code where NHiberate renames the aliases for joined tables when generating the SQL.

Every projection alias is amended with an index of the alias and an _.

So in your case, you can use

a1_.numerical_answer

If you added more aliases, you could use them in the SQL projections like this:

b2_.some_property, c3_.another_property, etc.

Notice that the order matters in which aliases are created. In my case I had a table with many joins in user-defined order and occurence, so I needed to resort to this and it works great.

Saddle