nhibernate queryover LIKE with expression trees

Cole W picture Cole W · Jan 17, 2012 · Viewed 14k times · Source

I'm looking to add a method to my base repository class that allows me to use LIKE expressions but I'm not quite sure of how to go about this. I want to create a generic method that looks at the expression tree passed in and looks for wildcard characters in the string values passed in. It would then generate the QueryOver statement accordingly.

I have the following currently:

public IList<T> FindAll(Expression<Func<T, bool>> criteria, char wildCard)
{
    return SessionFactory.GetCurrentSession()
            .QueryOver<T>()
            .Where(criteria)
            .List();
}

Obviously the hard part is yet to come. I need to look through the expression tree and build the query using QueryOver dynamically. Looking for some pointers on how to proceed with this. Or am I just wasting my time here and should just create individual methods in my repositories that handle the LIKE queries?

Additional Criteria

Ideally I'd like to tell the difference between the following:

  • search*
  • *search
  • *search*

So the query generated would be:

  • field LIKE 'search%'
  • field LIKE '%search'
  • field LIKE '%search%'

Answer

Phill picture Phill · Jan 17, 2012

There's two ways to write a Like expression in QueryOver.

If you do it off the Where clause:

.Where(Restrictions.Like(Projections.Property<T>(*projected property*), *string value*, MatchMode.Anywhere))

However this is kinda long to write.

So you can use WhereRestrictionOn:

.WhereRestrictionOn(*projected property*).IsLike(*string value*, MatchMode.Anywhere)

This means you need to pass in two parameters like:

FindAll<User>(x => x.FirstName, "bob");

You may be able to use .Contains, .StartsWith, .EndsWith, but I'm not sure.

FindAll<User>(x => x.FirstName.Contains("bob"));
FindAll<User>(x => x.FirstName.StartsWith("bob"));
FindAll<User>(x => x.FirstName.EndsWith("bob"));

I don't think those work in NHibernate.

Hope that helps.