How to do a proper search with nhibernate

Question

Hello everyone,

i'm working on a small project that is supposed to allow basic searches of the database. Currently i'm using nhibernate for the database interaction. In the database i have 2 tables: Person and Address. The Person table has a many-to-one relationship with Address.

The code i've come up with for doing searches is:

public IList<T> GetByParameterList(List<QueryParameter> parameterList)
    {
        if (parameterList == null)
        {
            return GetAll();
        }
        using (ISession session = NHibernateHelper.OpenSession())
        {
            ICriteria criteria = session.CreateCriteria<T>();
            foreach (QueryParameter param in parameterList)
            {
                switch (param.Constraint)
                {
                    case ConstraintType.Less:
                        criteria.Add(Expression.Lt(param.ParameterName, param.ParameterValue));
                        break;
                    case ConstraintType.More:
                        criteria.Add(Expression.Gt(param.ParameterName, param.ParameterValue));
                        break;
                    case ConstraintType.LessOrEqual:
                        criteria.Add(Expression.Le(param.ParameterName, param.ParameterValue));
                        break;
                    case ConstraintType.EqualOrMore:
                        criteria.Add(Expression.Ge(param.ParameterName, param.ParameterValue));
                        break;
                    case ConstraintType.Equals:
                        criteria.Add(Expression.Eq(param.ParameterName, param.ParameterValue));
                        break;
                    case ConstraintType.Like:
                        criteria.Add(Expression.Like(param.ParameterName, param.ParameterValue));
                        break;
                }
            }

            try
            {
                IList<T> result = criteria.List<T>();
                return result;
            }
            catch
            {
                //TODO: Implement some exception handling
                throw;
            }
        }
    }

The query parameter is a helper object that i use to create criterias and send it to the dal, it looks like this:

public class QueryParameter
{
    public QueryParameter(string ParameterName, Object ParameterValue, ConstraintType constraintType)
    {
        this.ParameterName = ParameterName;
        this.ParameterValue = ParameterValue;
        this.Constraint = constraintType;
    }

    public string ParameterName
    {
        get;
        set;
    }

    public Object ParameterValue
    {
        get;
        set;
    }

    public ConstraintType Constraint
    {
        get;
        set;
    }
}

Now this works well if i'm doing a search like FirstName = "John" , but not when i try to give a parameter like Street = "Some Street". It seems that nhibernate is looking for a street column in the Person table but not in the Address table.

Any idea on how should i change my code for so i could do a proper search? Tips? Maybe some alternatives?

Disclaimer: i'm kind of a noob so please be gentle ;) Thanks, Denis.

Answer 1

You could use an hql query like this

from Person p join Address a where a.Street = :street

And set the :street parameter with the query.

I've found that using hql is much easier than dealing with the criteria stuff, especially when you're new. Your first instinct is to standardize your nhibernate usage, but while you're learning it, it's best to keep your code close to the nhibernate API and not write too many helper methods.

Answer 2

It is looking for the Street property in the Person entity (notice ORM language... there are no tables and columns ;-)) because of this line:

ICriteria criteria = session.CreateCriteria<T>();

I assume that your type T is Person, and so your criteria assumes that the constraints that you are adding refer to properties in Person.

If you pass in a parameter that you know is a filter on the Address entity, you need to call CreateAlias(...) to "join" to the Address.

Answer 3

I would do a strongly typed search class for each one my domain object class. (using code generation against my domain objects for instance)

I would have an abstract class to put the ICriteriaBuilder logic using something very close to your current code then going recursive against any inner ICriteriaBuilder property

I would have something like that pseudo code :

class SearchCriteriaBuilder
public ConstraintType constraintType
public ICriteria Build(ISearchCriteriaBuilder pSearchCriteria)
{'your code goes there'}

class AdressSearchCriteriaBuilder : SearchCriteriaBuilder
public StringSearchCriteriaBuilder street;
public IntegerSearchCriteriaBuilder number;

class PersonSearchCriteriaBuilder : SearchCriteriaBuilder
public StringSearchCriteriaBuilder name;
public AdressSearchCriteriaBuilder adress;

Answer 4

Have a look at this video at 1:57 : http://skillsmatter.com/podcast/open-source-dot-net/nhibernate-tutorial-by-ayende-rahien

Ayende (most famous nhibernate expert) tells you how to build dynamic queries using criteria api.

Watch for the foreach explanation coming at 2:04:30