Wildcard (*,!) on search using Regex

Question

I'm new in MVC, I want to perform Wildcard (*, !) search on database. This is what I have done by using Regex:

Controller:

using System.Linq;
using System.Text.RegularExpressions;
using System.Web.Mvc;
using WebApplication1.Models;


namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {

        CrossWord_dbEntities db = new CrossWord_dbEntities();

        public ActionResult Index(string searching)
        {
            var regEx = WildcardToRegex(searching);
            return View(db.tbl_values.Where(x => Regex.IsMatch(x.Name, regEx, RegexOptions.Singleline)));//.ToList()));
        }

public static string WildcardToRegex(string pattern)
        {
            return "^" + Regex.Escape(pattern).
            Replace("\\*", ".*").
            Replace("\\?", ".") + "$";
        }

    }
}

View:

@model IEnumerable<WebApplication1.Models.tbl_values>

<br /><br />
@using (Html.BeginForm("Index", "Home", FormMethod.Get))
{
@Html.TextBox("searching") <input type="submit" value="Search" />
}

<table class="table table-striped">
    <thead>
        <tr>
            <th>Results</th>
        </tr>
    </thead>
    <tbody>
        @if (Model.Count() == 0)
        {
            <tr>
                <td colspan="3" style="color:red">
                    No Result 
                </td>
            </tr>
        }
        else
        {
            foreach (var item in Model)
            {
                <tr>
                    <td>
                        @item.Name
                    </td>
                </tr>
            }
        }

    </tbody>

</table>

With the above code when i run i get an exception in line: "@if (Model.Count() == 0)"

LINQ to Entities does not recognize the method 'Boolean IsMatch(System.String, System.String, System.Text.RegularExpressions.RegexOptions)' method, and this method cannot be translated into a store expression.

what i have to do to fix this problem?

i tried to write in the return line of the controller adding "ToList()"

return View(db.tbl_values.Where(x => Regex.IsMatch(x.Name, regEx, RegexOptions.Singleline).ToList()));

but i get the same expction on this line.

thanks Amir

score 1 · Accepted Answer · answered Dec 11 '17 at 01:36

The exception message is straightforward: Regex.IsMatch() method has no equivalent SQL statement when executing query in LINQ to Entities mode. You need to put AsEnumerable() or ToList() method before Where clause to materialize DbContext entity into IEnumerable object:

public class HomeController : Controller
{
    CrossWord_dbEntities db = new CrossWord_dbEntities();

    public ActionResult Index(string searching)
    {
        var regEx = WildcardToRegex(searching);
        return View(db.tbl_values.AsEnumerable().Where(x => Regex.IsMatch(x.Name, regEx, RegexOptions.Singleline)));
    }
}

Since you have WildcardToRegex method which only works in IEnumerable or LINQ to Objects, you can try adding other methods as described in this solution for use with IQueryable entities as follows:

public static IQueryable<TSource> WhereLike<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, string>> valueSelector, string value, char wildcard)
{
    return source.Where(BuildLikeExpression(valueSelector, value, wildcard));
}

private static Expression<Func<TElement, bool>> BuildLikeExpression<TElement>(Expression<Func<TElement, string>> valueSelector, string value, char wildcard)
{
    if (valueSelector == null) throw new ArgumentNullException("valueSelector");

    var method = GetLikeMethod(value, wildcard);

    value = value.Trim(wildcard);
    var body = Expression.Call(valueSelector.Body, method, Expression.Constant(value));

    var parameter = valueSelector.Parameters.Single();
    return Expression.Lambda<Func<TElement, bool>>(body, parameter);
}

private static MethodInfo GetLikeMethod(string value, char wildcard)
{
    var methodName = "Equals";

    var textLength = value.Length;
    value = value.TrimEnd(wildcard);
    if (textLength > value.Length)
    {
        methodName = "StartsWith";
        textLength = value.Length;
    }

    value = value.TrimStart(wildcard);
    if (textLength > value.Length)
    {
        methodName = (methodName == "StartsWith") ? "Contains" : "EndsWith";
    }

    var stringType = typeof(string);
    return stringType.GetMethod(methodName, new[] { stringType });
}

Then use WhereLike method for any entities implementing IQueryable like example below:

// usage example
using (CrossWord_dbEntities db = new CrossWord_dbEntities())
{
    var query = db.tbl_values.WhereLike(x => x.Name, "[search text]", "[wildcard character]");
}

Similar issue:

linq to entities doesn't recognize a method

Wildcard (*,!) on search using Regex

1 Answers1