如何使用 NHibernate 标准减少参数数量

本文关键字：参数 数数 何使用 NHibernate 标准 | 更新日期: 2023-09-27 18:31:04

我正在尝试查询一个包含25 字段名称 '字段名称 1， 字段名称 2 ...字段名称25' 和25 字段值 '字段值字符串 1， 字段值字符串 2 ...字段值字符串25'

不幸的是，我无法更改设计，只能更改我们查询数据的方式。

现在我们像这样查询它：

static DetachedCriteria CreateFieldValueBagDetachedCriteria(string field, object value)
{
    ICriterion criterionBag = Expression.Sql("0=1");
    for (int slotNr = 1 ; slotNr <= 25 ; slotNr++) 
    {
        ICriterion valueEqCritBag = Restrictions.InsensitiveLike(string.Format("FieldValueString{0}", slotNr), returnWildCardString( (string)value), MatchMode.Exact);
        ICriterion fieldNameEqCrit = Restrictions.Eq("FieldName" + slotNr, field);
        criterionBag = Restrictions.Or(Restrictions.And(fieldNameEqCrit, valueEqCritBag), criterionBag);
    }
    return criterionBag;
}

这将导致 50 个参数，这些参数都将是相同的 2 个值 25 次。

有没有办法重写此代码以减少参数的数量？

如何使用 NHibernate 标准减少参数数量

我能想到的最简单的方法是编写一个共享参数的特殊投影。但是，由于 LikeExpression 不将投影作为值，我们也必须编写自己的投影：

[Serializable]
public class SharingConstantProjection : SimpleProjection
{
    private readonly TypedValue _typedValue;
    private NHibernate.SqlCommand.Parameter _parameter;
    public SharingConstantProjection(object value)
        : this(value, NHibernateUtil.GuessType(value.GetType()))
    {
    }
    public SharingConstantProjection(object value, IType type)
    {
        _typedValue = new TypedValue(type, value, EntityMode.Poco);
    }
    public override bool IsAggregate
    {
        get { return false; }
    }
    public override SqlString ToGroupSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        throw new InvalidOperationException("not a grouping projection");
    }
    public override bool IsGrouped
    {
        get { return false; }
    }
    public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        if (_parameter == null)
            _parameter = criteriaQuery.NewQueryParameter(_typedValue).Single();
        return new SqlString(
            _parameter,
            " as ",
            GetColumnAliases(position, criteria, criteriaQuery)[0]);
    }
    public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return new IType[] { _typedValue.Type };
    }
    public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return new TypedValue[] { _typedValue };
    }
    public override string ToString()
    {
        return (_typedValue.Value ?? "NULL").ToString();
    }
}
[Serializable]
public class MyInsensitiveLikeExpression : AbstractCriterion
{
    private readonly IProjection _projection;
    private readonly IProjection _value;
    public MyInsensitiveLikeExpression(string propertyName, string value)
        : this(Projections.Property(propertyName), Projections.Constant(value))
    { }
    public MyInsensitiveLikeExpression(string propertyName, IProjection value)
        : this(Projections.Property(propertyName), value)
    { }
    public MyInsensitiveLikeExpression(IProjection projection, IProjection value)
    {
        _projection = projection;
        _value = value;
    }
    #region ICriterion Members
    public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        SqlString[] columns = CriterionUtil.GetColumnNames(null, _projection, criteriaQuery, criteria, enabledFilters);
        if (columns.Length != 1)
            throw new HibernateException("Like may only be used with single-column properties / projections.");
        var value = SqlStringHelper.RemoveAsAliasesFromSql(_value.ToSqlString(criteria, 0, criteriaQuery, enabledFilters));
        var dialect = criteriaQuery.Factory.Dialect;
        var builder = new SqlStringBuilder(8)
            .Add(dialect.LowercaseFunction)
            .Add(StringHelper.OpenParen)
            .Add(columns[0])
            .Add(StringHelper.ClosedParen)
            .Add(" like ")
            .Add(dialect.LowercaseFunction)
            .Add(StringHelper.OpenParen)
            .Add(value)
            .Add(StringHelper.ClosedParen);
        return builder.ToSqlString();
    }
    public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return _value.GetTypedValues(criteria, criteriaQuery);
    }
    public override IProjection[] GetProjections()
    {
        return new IProjection[] { _projection };
    }
    #endregion
    public override string ToString()
    {
        return _projection + " like " + _value;
    }
}

并像使用它一样使用

var foo = new SharingConstantProjection("foo");
session.CreateCriteria<User>()
    .Add(new MyInsensitiveLikeExpression(Projections.Property<User>(u => u.Name), foo) || new MyInsensitiveLikeExpression(Projections.Property<User>(u => u.Email), foo))
    .List();
相关文章：