Linq, Expressions, NHibernate and Like comparison

Question

I am trying to do a like comparison based on an outside parameter (passed by a search form) that determines type of comparison ("%string" or "string%" or "%string%")

I was thinking in the following direction:

query = query.Where(
Entity.StringProperty.Like("SearchString", SelectedComparsionType)
)

Like method would than based on selected type return .StartsWith() or .EndsWith or .SubString

My knowledge of expressions is apparently far from great, since i haven't been able to construct a method that could yield the right result (server side comparison in SQL just like with StartsWith method).

Answer 1

You will be better off using Regex to solve this problem.

Answer 2

Sounds like you should be wanting to use:

query = query.Where(
Entity.StringProperty.Contains("SearchString")
)

This should map to:

WHERE StringProperty LIKE '%SearchString%'

This should also work for more advanced search masks such as "Mr? Sm%th", but I haven't had to test any search strings like that myself yet.

---

UPDATE: Based on OPs edit

It sounds like what you are asking for is something like the following:

   public enum SelectedComparsionType
    {
        StartsWith,
        EndsWith,
        Contains
    }

public static bool Like(this string searchString, string searchPattern, SelectedComparsionType searchType)
{
    switch (searchType)
    {
        case SelectedComparsionType.StartsWith:
            return searchString.StartsWith(searchPattern);
        case SelectedComparsionType.EndsWith:
            return searchString.EndsWith(searchPattern);
        case SelectedComparsionType.Contains:
        default:
            return searchString.Contains(searchPattern);
    }
}

This would allow you to write code as you require, i.e:

query = query.Where(
Entity.StringProperty.Like("SearchString", SelectedComparsionType.StartsWith)
)

However, personally, I would replace any use of SelectedComparsionType, with a direct call to the required string function. I.e

query = query.Where(
Entity.StringProperty.StartsWith("SearchString")
)

As this will still map to a SQL 'LIKE' query.

Answer 3

Thanks for the answers. I would like to clarify a bit further on what i had in mind:

The following statement (or something similar if this is not possibile):

query = query.Where(
        Entity.StringProperty.Like("SearchString", SelectedComparsionType)
        );

should be translated into (and shoud run on SQL server as: like "SearchString%"):

query = query.Where()
Entity.StringProperty.StarsWith("SearchString");

if SelectedComparsionType eaquals BeginingOfString

or should be translated into (and shoud run on SQL server as: like "%SearchString"):

query = query.Where()
Entity.StringProperty.EndsWith("SearchString");

if SelectedComparsionType eaquals EndOfString

or should be translated into (and shoud run on SQL server as: like "%SearchString%"):

query = query.Where()
Entity.StringProperty.Contains("SearchString");

if SelectedComparsionType eaquals AnywhereInString

I just don't know how to write the extension method or equivalent static helper method that would give this result.

Answer 4

This is exactly what I had in mind, thank you. I had something similar already written, but it didn't translate to SQL. For example, it worked if I did this directly:

Entity.StringProperty.EndsWith("SearchString");

It didn't work if I used a dedicated method:

CompMethod("BaseString","SearchString",SelectedComparsionType.EndsWith)

I think it probably has something to do with expression evaluation, i'm just not sure what.

Answer 5

The easy way

Just use

if (comparison == ComparisonType.StartsWith)
    query = query.Where(e => e.StringProperty.StartsWith("SearchString"));
else if ...

The hard way

If you want to do something like this, either make sure your LINQ provider can be told of this new method somehow, and how it translates to SQL (unlikely), or prevent your method from ever reaching the LINQ provider, and provide the provider something it understands (hard). For example, instead of

query.Where(e => CompMethod(e.StringProperty, "SearchString", comparsionType))

you can create something like

var query = source.WhereLike(e => e.StringProperty, "SearchString", comparsionType)

with the following code

public enum ComparisonType { StartsWith, EndsWith, Contains }

public static class QueryableExtensions
{
    public static IQueryable<T> WhereLike<T>(
        this IQueryable<T> source,
        Expression<Func<T, string>> field, 
        string value,
        SelectedComparisonType comparisonType)
    {
        ParameterExpression p = field.Parameters[0];
        return source.Where(
            Expression.Lambda<Func<T, bool>>(
                Expression.Call(
                    field.Body, 
                    comparisonType.ToString(), 
                    null, 
                    Expression.Constant(value)),
            p));
    }
}

You can even add additional criteria this way

var query = from e in source.WhereLike(
                e => e.StringProperty, "SearchString", comparsionType)
            where e.OtherProperty == 123
            orderby e.StringProperty
            select e;

The very, very hard way

It would (technically) be possible to rewrite the expression tree before the provider sees it, so you can use the query you had in mind in the first place, but you'd have to

• create a Where(this IQueryable<EntityType> source, Expression<Func<EntityType, bool>> predicate) to intercept the Queryable.Where,
• rewrite the expression tree, replacing your CompMethod, wherever it is, with one of the String methods,
• call the original Queryable.Where with the rewritten expression,
• and first of all, be able to follow the extension method above in the first place!

But that's probably way too complicated for what you had in mind.