Create SqlProjection with alias of joined table

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[] { "Yes开发者_如何学GoAnswers" }, 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

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"

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.