nhibernate queryover LIKE with expression trees

Question

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%'

Fiddlehead

Answer 1

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.

Meshach

Answer 2

I don't really understand what you want to do. Do you want for a query such as

session.QueryOver<T>().Where(x => x.property == "*substring*").List();

to generate a property LIKE "%substring%" query? In most Linq providers the String.Contains method is transformed in a "LIKE" query, and thus you wouldn't need to look for wildcard characters in the expression tree, only for the String.Contains method.
In case of the latter, you would have to parse the expression tree looking for a String.Contains() method. This could be very troublesome (http://msdn.microsoft.com/en-us/library/bb397951.aspx). Also, I can't see in your method which property is to be "compared" with the LIKE operator.

Anyways, I think it would be easier to pass a ICriterion to your .Where(), such as

.Where(new NHibernate.Criterion.LikeExpression("property", "%value%"))

, and append your other conditions with .And() right after that. The drawback is losing strongly-typed queries.

Punctuation

I still don't know if you are using String.Contains inside the expression or a simple equality to signal the use of the LIKE operator. Still, you would have to parse the expression tree and integrate this with NHibernate through a combination of .Where() and .And(). Unless I'm missing something here, it is definitely not worth it. You could use somewhat limited LIKE queries with strongly-typed Linq-to-NH, doing something like session.Query<T>().Where(x => x.property.Contains("substring")). This would obviously only search for "%substring%", which wouldn't make up for all your combinations. – Punctuation

Answer 3

After digging for a while for a solution about the problem of translating expressions of the form

session.QueryOver<T>().Where(x => x.StringAttrbute.StartsWith("ajoofa"))

into SQL of the form

SELECT * FROM {table} WHERE {string_attribute} LIKE 'ajoofa%'

I came up with the following solution: Yu have to Register custom method calls for the Standard string functions .Contains(), .StartsWith, .EndsWith(). God knows why these functions are not registered by default within NHibernate. The following code should help you out.

/// Perform the registration of custom methods
/// </summary>
public static void Register()
{
    if (!_registered)
    {
    _registered = true;
    String str = null;
    ExpressionProcessor.RegisterCustomMethodCall(() => str.StartsWith(null), ProcessStartsWith);
    ExpressionProcessor.RegisterCustomMethodCall(() => str.EndsWith(null), ProcessEndsWith);
    ExpressionProcessor.RegisterCustomMethodCall(() => str.Contains(null), ProcessContains);
    }
}

static ICriterion ProcessStartsWith(MethodCallExpression methodCallExpression)
{
    ExpressionProcessor.ProjectionInfo projection = ExpressionProcessor.FindMemberProjection(methodCallExpression.Object);
    object value = ExpressionProcessor.FindValue(methodCallExpression.Arguments[0]) + "%";
    return projection.CreateCriterion(Restrictions.Like, Restrictions.Like, value);
}

static ICriterion ProcessEndsWith(MethodCallExpression methodCallExpression)
{
    ExpressionProcessor.ProjectionInfo projection = ExpressionProcessor.FindMemberProjection(methodCallExpression.Object);
    object value = "%" + ExpressionProcessor.FindValue(methodCallExpression.Arguments[0]);
    return projection.CreateCriterion(Restrictions.Like, Restrictions.Like, value);
}

static ICriterion ProcessContains(MethodCallExpression methodCallExpression)
{
    ExpressionProcessor.ProjectionInfo projection = ExpressionProcessor.FindMemberProjection(methodCallExpression.Object);
    object value = "%" + ExpressionProcessor.FindValue(methodCallExpression.Arguments[0]) + "%";
    return projection.CreateCriterion(Restrictions.Like, Restrictions.Like, value);
}

Michaelis