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///&nbsp; result of a subquery/// Note: DB support of row value constructor is required/// </summary>[Serializable]public class MultiPropertiesSubqueryExpression : SubqueryExpression{&nbsp; &nbsp; private readonly string[] _propertyNames;&nbsp; &nbsp; public MultiPropertiesSubqueryExpression(string[] propertyNames, string op, DetachedCriteria dc)&nbsp; &nbsp; &nbsp; &nbsp; : base(op, null, dc)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; _propertyNames = propertyNames;&nbsp; &nbsp; }&nbsp; &nbsp; protected override SqlString ToLeftSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; return new SqlString("(", string.Join(", ", _propertyNames.Select(pn => criteriaQuery.GetColumns(criteria, pn)).SelectMany(x => x)), ")");&nbsp; &nbsp; }}以及用法示例：DetachedCriteria detachedCriteria = DetachedCriteria.For(typeof(Bar))&nbsp; &nbsp; //.Add(...) //Add some conditions&nbsp; &nbsp; .SetProjection(Projections.ProjectionList().Add(Property.ForName("Prop1")).Add(Property.ForName("Prop2")));var result = session.CreateCriteria(typeof(Foo))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Add(new MultiPropertiesSubqueryExpression(new[] {"Prop1", "Prop2"}, "in", detachedCriteria))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .List<Foo>();