NHibernate ICriteria query with components and collections for advanced search

Question

I'm building an advanced search form for my ASP.NET MVC app.

I have a Customer object, with an Address Component: Fluent NHibernate mapping:

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

In my Customer class ctor, to prevent null objects, I have the following:

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

My search form has the following fields available to the user to search on:

• Customer Name
• City
• State
• Industry

All of these fields are optional.

My NHibernate Criteria looks like this (customer is being passed in from the form using the ASP.NET MVC Model Binder):

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

Notice that I'm using .ExcludeZeroes() to exclude nulls and zero-defaulted values. This is required since my Customer object has some INTs (excluded in this post for brevity) that would default to zero (0) in the query, resulting in incorrect queries.

If I run this with all the fields blank (ok, since they are optional), the resulting SQL looks like this:

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

Industry and State are drop-downs in the web form, but in the above example, I left them blank. But the ExcludeZeroes() declaration doesn't seem to apply to those fields.

If I manually check before the Criteria:

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

and do the same for Industry, the Criteria will then work.

I'm assuming this has to do with me initializing the Address object in my Customer ctor. I hate to change that, but I don't know of another way of making the Criteria work without manually checking for empty string values from the form (thus eliminating the advantage to using a Example object with ICriteria).

Why? How can I get this Criteria query to work?

Answer 1

I have the same issue with QBE. I also think that the Query by Example is a very nice approach for generic searches on an object (and associates). There is already ExcludeNones/Nulls/Zeros. There should be an option to exclude empty strings ("") too.

Answer 2

Use a property selector to ignore null or empty strings.

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 ]
    }

}