NHibernate HQL Inner Join (SQL Server,Visual C#)

Question

I want to using HQL with inner Join. But, a query syntax exception is thrown.

This is my C# code:

string sqlQuery = "Select fq FROM Answers as fq INNER JOIN Questions as q " +
    " on fq.questionId=q.questionId";

IList Result;
int count = 0;

try
{
    using (ISession session = ConnectionModule.OpenSession())
    {
        IQuery query = session.CreateQuery(sqlQuery);
        session.CreateCriteria(typeof(Answers));
        Result = query.List();
    }
}
catch(Exception ex) 
{
    MessageBox.Show(ex.Message+"\n"+ex.InnerException);
}

Answer 1

The point here is

• CROSS JOIN if there are no mapped relations,
• JOIN on existing (mapped) relations.

So, in case, there is no mapped relation Question to Answer - we still can query it like this:

// instead of INNER JOIN we use 'comma' to produce CROSS JOIN
// instead of ON we need WHERE
// string sqlQuery = "Select fq FROM Answers as fq, INNER JOIN Questions as q "+ 
//  "on fq.questionId=q.questionId";

string sqlQuery = "Select fq FROM Answers as fq, Questions as q " +
    " WHERE fq.questionId=q.questionId";

In case we have mapping Answer.Question and IList<Answer> Question.Answers

// the Reference (C#) is the way how to express ON
string sqlQuery = "Select fq FROM Answers as fq INNER JOIN fq.Questions as q";

Check the

• 14.2. The from clause (details about CROSS JOIN)
• 14.3. Associations and joins (standard JOIN related to references)