代码之家 › 专栏 › 技术社区 › Todd Brooks

NHibernate ICriteria查询,包含用于高级搜索的组件和集合

icriteria fluent-nhibernate nhibernate

2

Todd Brooks · 技术社区 · 17 年前

我正在为我的ASP.NET MVC应用程序构建高级搜索表单。

我有一个客户对象,带有地址组件: Fluent NHibernate映射:

       public CustomerMap()
    {
        WithTable("Customers");

        Id(x => x.Id)
            .WithUnsavedValue(0)
            .GeneratedBy.Identity();

        Map(x => x.Name);
        Map(x => x.Industry);

        Component(x => x.Address, m =>
        {
            m.Map(x => x.AddressLine);
            m.Map(x => x.City);
            m.Map(x => x.State);
            m.Map(x => x.Zip);
        });

public Customer()
{
    Address = new Address();
}

“我的搜索表单”有以下字段可供用户搜索:

状态

所有这些字段都是可选的。

我的NHibernate标准如下所示(正在使用ASP.NET MVC模型绑定器从表单传递客户):

            var p = Session.CreateCriteria(typeof(Customer))
            .Add(Example.Create(customer).ExcludeZeroes().IgnoreCase().EnableLike())
            .SetProjection(Projections.ProjectionList()
                               .Add(Projections.Property("Id"), "Id")
                               .Add(Projections.Property("Name"), "Name")
                               .Add(Projections.Property("Address.City"), "City")
                               .Add(Projections.Property("Address.State"), "State")
                               .Add(Projections.Property("PhoneNumber"), "PhoneNumber"))
            .AddOrder(Order.Asc("Name"))
            .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(CustomerDTO)));

        return p.List<CustomerDTO>() as List<CustomerDTO>;

请注意,我使用.ExcludeZeroes()来排除null和零默认值。这是必需的,因为我的客户对象有一些int(为了简洁起见,本文中排除了这些int),这些int在查询中会默认为零(0),从而导致不正确的查询。

如果在所有字段都为空的情况下运行此操作(确定,因为它们是可选的),则生成的SQL如下所示:

SELECT   this_.Id          as y0_,
         this_.Name        as y1_,
         this_.City        as y2_,
         this_.State       as y3_,
         this_.PhoneNumber as y4_
FROM     Customers this_
WHERE    (lower(this_.Industry) like '' /* @p0 */
          and lower(this_.State) like '' /* @p1 */)
ORDER BY y1_ asc

行业和州是web表单中的下拉列表,但在上面的示例中,我将它们留空。但是ExcludeZeroes()声明似乎不适用于这些字段。

如果我在条件之前手动检查:

if (customer.Address.State == "")
{
    customer.Address.State = null;
}

对工业界也是如此,这样标准就会起作用。

我假设这与我在客户ctor中初始化Address对象有关。我不想改变这一点,但我不知道还有什么方法可以让标准在不手动检查表单中的空字符串值的情况下工作(这样就消除了在ICriteria中使用示例对象的优势)。

为什么?如何使此条件查询工作?

2 回复 | 直到 17 年前

1

quip 17 年前

使用属性选择器忽略空字符串或空字符串。

using System;
using NHibernate.Criterion;
using NHibernate.Type;


namespace Sample
{

    /// <summary>
    /// Implementation of <see cref="Example.IPropertySelector"/> that includes the
    /// properties that are not <c>null</c> and do not have an <see cref="String.Empty"/>
    /// returned by <c>propertyValue.ToString()</c>.
    /// </summary>
    /// <remarks>
    /// This selector is not present in H2.1. It may be useful if nullable types
    /// are used for some properties.
    /// </remarks>
    public class NoValuePropertySelector : Example.IPropertySelector
    {
        #region [ Methods (2) ]

        // [ Public Methods (1) ]

        /// <summary>
        /// Determine if the Property should be included.
        /// </summary>
        /// <param name="propertyValue">The value of the property that is being checked for inclusion.</param>
        /// <param name="propertyName">The name of the property that is being checked for inclusion.</param>
        /// <param name="type">The <see cref="T:NHibernate.Type.IType"/> of the property.</param>
        /// <returns>
        ///     <see langword="true"/> if the Property should be included in the Query,
        /// <see langword="false"/> otherwise.
        /// </returns>
        public bool Include(object propertyValue, String propertyName, IType type)
        {
            if (propertyValue == null)
            {
                return false;
            }

            if (propertyValue is string)
            {
                return ((string)propertyValue).Length != 0;
            }

            if (IsZero(propertyValue))
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        // [ Private Methods (1) ]

        private static bool IsZero(object value)
        {
            // Only try to check IConvertibles, to be able to handle various flavors
            // of nullable numbers, etc. Skip strings.
            if (value is IConvertible && !(value is string))
            {
                try
                {
                    return Convert.ToInt64(value) == 0L;
                }
                catch (FormatException)
                {
                    // Ignore
                }
                catch (InvalidCastException)
                {
                    // Ignore
                }
            }

            return false;
        }


        #endregion [ Methods ]
    }

}

2

0

Amin Emami Amin Emami 17 年前

我对QBE也有同样的问题。我还认为,对于对象(和关联对象)的一般搜索,示例查询是一种非常好的方法。已存在ExcludeNones/Nulls/Zero。还应该有一个选项来排除空字符串(“”)。