Detached criteria - how to to specify join conditions

Question

I am trying to retrieve some data from database using Nhibernate detached criteria. The problem is that I dont know how to specify the join conditions in Detached criteria. Please find my code below,

var criteria = DetachedCriteria.For<ExtraRiskQuestionAnswer>("extraRiskQuestionAnswer")
                .CreateAlias("RefRiskQuestion", "refRiskQuestion", JoinType.RightOuterJoin)
                .Add( Restrictions.Disjunction().
                     Add(Restrictions.Eq("CRMContactId", crmContactId))
                     .Add(Restrictions.IsNull("CRMContactId")));

This gets translated to -

SELECT refriskque1_.RefRiskQuestionId as y0_, refriskque1_.Question as y1_, this_.Answer as y2_ 
FROM FactFind.dbo.TExtraRiskQuestionAnswer this_ 
right outer join Administration.dbo.TRefRiskQuestion refriskque1_ on this_.RefRiskQuestionId=refriskque1_.RefRiskQuestionId 

WHERE this_.CRMContactId = 4670861 or this_.CRMContactId is null

But What I want is it should get translated to below.

SELECT refriskque1_.RefRiskQuestionId as y0_, refriskque1_.Question as y1_, this_.Answer as y2_ 
FROM FactFind.dbo.TExtraRiskQuestionAnswer this_ 
right outer join Administration.dbo.TRefRiskQuestion refriskque1_ on this_.RefRiskQuestionId=refriskque1_.RefRiskQuestionId 
and this_.CRMContactId = 4670861 or this_.CRMContactId is null

Any help much appreciated. Thanks.

score 1 · Accepted Answer · answered May 17 '13 at 12:21

NHiberante is really powerful. One of overloads of the method you used CreateAlias looks like this:

public DetachedCriteria CreateAlias(string associationPath
, string alias
, JoinType joinType
, ICriterion withClause);

The most interesting part in this case is the last withClause. This is in fact definition of conditions to be place directly into the JOIN clause

So what you should do is somehting like this:

var restriction = Restrictions.Disjunction()
  .Add(Restrictions.Eq("CRMContactId", crmContactId))
  .Add(Restrictions.IsNull("CRMContactId")));

And having this just adjust the defintion of the DetachedCriteria:

var criteria = DetachedCriteria.For<ExtraRiskQuestionAnswer>("extraRiskQuestionAnswer")
  .CreateAlias("RefRiskQuestion"
  , "refRiskQuestion"
  , JoinType.RightOuterJoin
  , restriction ) // HERE we go, that will be placed where you expected

And now you should have what you need. Other words, any additional restrictions for JOIN clause must be placed into WithClause .

Thanks a lot it worked. But we need to use extraRiskQuestionAnswer.CRMContactId in the restrictions as it couldn't able to resolve the CRMContactId. — Bhaskar, May 17 '13 at 12:43

Detached criteria - how to to specify join conditions

1 Answers1