IN clause mapping with multiple columns in NHibernate

Question

I am trying to write the Nhibernate ICriteria for the following.

SELECT * 
FROM foo 
WHERE 
  (fooKeyColumn1, fooKeyColumn2) IN (
    SELECT barKeyColumn1, barKeyColumn2 
    FROM bar
    WHERE <some conditions>)

If I need to check the IN for one column then I can do it using

Subqueries.PropertyIn(propertyName,detachedCriteria); 

but having trouble if I want to check the same for multiple properties like given in the above sql example.

Any NHibernate experts can guide me to accomplish this?

I want to map this subquery in my existing module which developed using ICriteria as a key component.

Answer 1

You need custom implementation of SubqueryExpression to achieve it:

/// <summary>
/// A comparison between multiple properties in the outer query and the
///  result of a subquery
/// Note: DB support of row value constructor is required
/// </summary>
[Serializable]
public class MultiPropertiesSubqueryExpression : SubqueryExpression
{
    private readonly string[] _propertyNames;

    public MultiPropertiesSubqueryExpression(string[] propertyNames, string op, DetachedCriteria dc)
        : base(op, null, dc)
    {
        _propertyNames = propertyNames;
    }

    protected override SqlString ToLeftSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return new SqlString("(", string.Join(", ", _propertyNames.Select(pn => criteriaQuery.GetColumns(criteria, pn)).SelectMany(x => x)), ")");
    }
}

And example of usage:

DetachedCriteria detachedCriteria = DetachedCriteria.For(typeof(Bar))
    //.Add(...) //Add some conditions
    .SetProjection(Projections.ProjectionList().Add(Property.ForName("Prop1")).Add(Property.ForName("Prop2")));

var result = session.CreateCriteria(typeof(Foo))
                    .Add(new MultiPropertiesSubqueryExpression(new[] {"Prop1", "Prop2"}, "in", detachedCriteria))
                    .List<Foo>();