Entity Framework wildcards & Linq

Question

Is it possible to construct a valid Linq query which contains wildcard characters?

I've seen various answers to this question which suggest using:

.Where(entity => entity.Name.Contains("FooBar"))
.Where(entity => entity.Name.EndsWith("Bar")) 
.Where(entity => entity.Name.StartsWith("Foo"))

OR constructing RawSql:

var commandText =
    @"SELECT field
    FROM     table
    WHERE    field LIKE @search";

var query = new ObjectQuery<Profile>(commandText, context);
query.Parameters.Add(new ObjectParameter("search", wildcardSearch));

The first solution wouldn't work if the wildcard was not at the beginning or end of a string, for example, searchTerm = "Foo%Bar".

The second solution, using RawSql, doesn't sit right with me and feels like a cheap way out. But it does work.

The third option I have yet to try out is to create something which can parse the search term and construct a valid Linq query, which is something @Slauma had a go at in link 2 below. But this still wouldn't work if the wildcard was not at the beginning or end of the search term.

So the question: Is it possible to construct a valid Linq query which contains wildcard characters?

EDIT: Its worth mentioning that in this instance im using Oracle Data Access Components (ODAC/ODP), but I don't think it makes much difference in this case.

links:

1.“like” queries in Entity Framework

2.exact and wildcard searching conditional on search term

3.Using RawSql

Answer 1

I found a great solution for Oracle. This is part from another answer here, and part written by me.

    public static class LinqExtensions
{
    public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, String Name, String value)
    {
        Type model = typeof(T);
        ParameterExpression param = Expression.Parameter(typeof(T), "m");
        PropertyInfo key = model.GetProperty(Name);
        MemberExpression lhs = Expression.MakeMemberAccess(param, key);
        Expression<Func<T, String>> lambda = Expression.Lambda<Func<T, String>>(lhs, param);

        return source.Where(BuildLikeExpression(lambda, value));
    }
    public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, Expression<Func<T, String>> valueSelector, String value)
    {
        return source.Where(BuildLikeExpression(valueSelector, value));
    }
    public static Expression<Func<T, Boolean>> BuildLikeExpression<T>(Expression<Func<T, String>> valueSelector, String value)
    {
        if (valueSelector == null)
            throw new ArgumentNullException("valueSelector");
        value = value.Replace("*", "%");        // this allows us to use '%' or '*' for our wildcard
        if (value.Trim('%').Contains("%"))
        {
            Expression myBody = null;
            ParsedLike myParse = Parse(value);
            Type stringType = typeof(String);
            if(myParse.startwith!= null)
            {
                myBody = Expression.Call(valueSelector.Body, stringType.GetMethod("StartsWith", new Type[] { stringType }), Expression.Constant(myParse.startwith));
            }
            foreach (String contains in myParse.contains)
            {
                if (myBody == null)
                {
                    myBody = Expression.Call(valueSelector.Body, stringType.GetMethod("Contains", new Type[] { stringType }), Expression.Constant(contains));
                }
                else
                {
                    Expression myInner = Expression.Call(valueSelector.Body, stringType.GetMethod("Contains", new Type[] { stringType }), Expression.Constant(contains));
                    myBody = Expression.And(myBody, myInner);
                }
            }
            if (myParse.endwith != null)
            {
                if (myBody == null)
                {
                    myBody = Expression.Call(valueSelector.Body, stringType.GetMethod("EndsWith", new Type[] { stringType }), Expression.Constant(myParse.endwith));
                }
                else
                {
                    Expression myInner = Expression.Call(valueSelector.Body, stringType.GetMethod("EndsWith", new Type[] { stringType }), Expression.Constant(myParse.endwith));
                    myBody = Expression.And(myBody, myInner);
                }
            }
            return Expression.Lambda<Func<T, Boolean>>(myBody, valueSelector.Parameters.Single());
        }
        else
        {
            Expression myBody = Expression.Call(valueSelector.Body, GetLikeMethod(value), Expression.Constant(value.Trim('%')));
            return Expression.Lambda<Func<T, Boolean>>(myBody, valueSelector.Parameters.Single());
        }
    }
    private static MethodInfo GetLikeMethod(String value)
    {
        Type stringType = typeof(String);

        if (value.EndsWith("%") && value.StartsWith("%"))
        {
            return stringType.GetMethod("Contains", new Type[] { stringType });
        }
        else if (value.EndsWith("%"))
        {
            return stringType.GetMethod("StartsWith", new Type[] { stringType });
        }
        else
        {
            return stringType.GetMethod("EndsWith", new Type[] { stringType });
        }
    }
    private class ParsedLike
    {
        public String startwith { get; set; }
        public String endwith { get; set; }
        public String[] contains { get; set; }
    }
    private static ParsedLike Parse(String inValue)
    {
        ParsedLike myParse = new ParsedLike();
        String work = inValue;
        Int32 loc;
        if (!work.StartsWith("%"))
        {
            work = work.TrimStart('%');
            loc = work.IndexOf("%");
            myParse.startwith = work.Substring(0, loc);
            work = work.Substring(loc + 1);
        }
        if (!work.EndsWith("%"))
        {
            loc = work.LastIndexOf('%');
            myParse.endwith = work.Substring(loc + 1);
            if (loc == -1)
                work = String.Empty;
            else
                work = work.Substring(0, loc);
        }
        myParse.contains = work.Split(new[] { '%' }, StringSplitOptions.RemoveEmptyEntries);
        return myParse;
    }
}

Answer 2

If you are using an EDMX file as the basis for your Entity Model then perhaps you could try creating a Conceptual Model Function that then performs a LIKE in SQL. I am not sure if this would work for Oracle. You should then be able to do something like the following:

.Where(entity => Like(entity.Name, "Foo%Bar"))

Answer 3

use SqlFunctions.PatIndex, it would look like this:

.Where(entity => SqlFunctions.PatIndex("Foo%Bar", entity.Name) > 0)