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

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"