NHibernate 中具有多列的 IN 子句映射

Question

我正在尝试为以下内容编写 Nhibernate ICriteria。SELECT *  FROM foo  WHERE    (fooKeyColumn1, fooKeyColumn2) IN (     SELECT barKeyColumn1, barKeyColumn2      FROM bar     WHERE <some conditions>)如果我需要检查一列的 IN，那么我可以使用Subqueries.PropertyIn(propertyName,detachedCriteria);但如果我想检查上面 sql 示例中给出的多个属性的相同属性，就会遇到麻烦。任何 NHibernate 专家可以指导我完成这个任务吗？我想将此子查询映射到使用 ICriteria 作为关键组件开发的现有模块中。

Answer 1

您需要自定义实现SubqueryExpression来实现它：

/// <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)), ")");

    }

}

以及用法示例：

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>();