LINQ to SQL query filter, with the name match ignoring multiple punctuation characters

Question

I have the following LINQ to SQL query which works fine but looks ugly:

var filter = "filter";

query = query.Where(x =>
    x.Name.Replace("'", "").Replace("\"", "").Replace("#", "").Replace("/", "").Replace("-", "").Contains(filter) ||
    x.FullName.Replace("'", "").Replace("\"", "").Replace("#", "").Replace("/", "").Replace("-", "").Contains(filter));

It'd be nice to be able to do something similar to this (which isn't possible because LINQ to Entities won't recognize the method):

var filter = "filter";
var removals = new string[] { "'", "\"", "#", "/", "-" };

query = query.Where(x =>
  Replaces(x.Name, removals).Contains(filter) ||
  Replaces(x.Full, removals).Contains(filter));

... but I can't figure out how that could be written. I've written predicates that dealt with entire expressions, but not with just a single property.

This is a LINQ to SQL expression, so I can't just pull it out into its own method or I'll get an error like:

Additional information: LINQ to Entities does not recognize the method 'System.String RemoveAll

Answer 1

You are rather removing those parts instead of replacing them, so more appropriate name would be remove all.

You can make your own extension method like this :

public static class Extensions
{
    public static string RemoveAll(this string source, string[] charsToRemove)
    {
        return charsToRemove.Aggregate(source, (current, t) => current.Replace(t, string.Empty));
    }
}

If you really want to replace them with something you can do it like this :

public static string ReplaceAll(this string source, string[] charsToRemove, string[] charsToReplace)
{
    string result = source;
    for (var i = 0; i < charsToRemove.Length; i++)
    {
        result = result.Replace(charsToRemove[i], charsToReplace[i]);
    }
    return result;
}

Example usage :

var filter = "filter";
string[] itemsToRemove = {"'", @"""",};

query = query.Where(x =>
    x.Name.RemoveAll(itemsToRemove).Contains(filter) ||
    x.FullName.RemoveAll(itemsToRemove).Contains(filter));

UPDATE

LINQ to SQL would require you to call .AsEnumerable(), .ToList() or .ToArray() first before operating on strings, you might loose some performance from that but the other way is to write your custom query provider or stick with what you have.

Answer 2

var filter = "filter";

query = query.Where(x =>
    x.Name.Replace("'", "").Replace("\"", "").Replace("#", "").Replace("/", "").Replace("-", "").Contains(filter) ||
    x.FullName.Replace("'", "").Replace("\"", "").Replace("#", "").Replace("/", "").Replace("-", "").Contains(filter));

If you need that many replacements for a simple search then I think either the data or the filter is broken.

I guess all those delimiters (?) have some meaning, usually they have and they look like they have, so try to build the filter according to the rules instead of changing the data to match the invalid filter.

Answer 3

You could create the following helper extension method:

private static string RemoveAll(this string text, IEnumerable<char> removals)
{
    return new string(text.ToCharArray().Except(removals).ToArray());
}

Then your code will looks like:

var removals = new [] { '\'', '"', '#', '/', '-' };

query = query.Where(x =>
    x.Name.RemoveAll(removals).Contains(filter) ||
    x.FullName.RemoveAll(removals).Contains(filter)).ToArray();

Sample test:

string s = "1'2'3#4-5";
var removals = new [] { '\'', '"', '#', '/', '-' };
Console.WriteLine(s.RemoveAll(removals));

Output:

12345

Answer 4

You could write extension method that combines Replaces and returns new query.

static class LinqExtensions
{
    public class Projection<T>
    {
        public T Item { get; set; }
        public string Field1 { get; set; }
        public string Field2 { get; set; }
    }

    public static IQueryable<T> ContainsEx<T>(this IQueryable<T> query, 
        string[] toRemove, string filter, Expression<Func<T, Projection<T>>> projection)
    {
        var projectionQuery = query.Select(projection);

        foreach (var str in toRemove)
        {
            projectionQuery = projectionQuery.Select(x => new Projection<T>
            {
                Field1 = x.Field1.Replace(str, ""),
                Field2 = x.Field2.Replace(str, ""),
                Item = x.Item
            });
        }

        return projectionQuery
            .Where(x => filter.Contains(x.Field1) || filter.Contains(x.Field2))
            .Select(x => x.Item);
    }
}

And use it:

            var removeCharacters = new[] { ",", "#", "/", "-" };

            var query = context.Accounts;
            var result = query.ContainsEx(removeCharacters, "filter", x => new LinqExtensions.Projection<Accounts>
            {
                Field1 = x.Name,
                Field2 = x.FullName,
                Item = x
            }).ToArray();

Answer 5

Here's a generic solution I created to solve these sorts of issues, and the specifics for this particular one. It uses an Attribute class to mark methods (normally extension methods) as needing special processing for LINQ to SQL/EF and an ExpressionVisitor to re-write the queries for each marked method.

First, the Attribute class:

[AttributeUsage(AttributeTargets.Method)]
public class ExpandMethodAttribute : Attribute {
    private string methodName;

    public ExpandMethodAttribute(string aMethodName = null) => methodName = aMethodName;

    public MethodInfo ExpandingMethod(MethodInfo mi) {
        var methodType = mi.DeclaringType;
        var origMethodName = mi.Name;
        var argTypes = new[] { typeof(Expression) }.Concat(mi.GetParameters().Skip(1).Select(pi => pi.ParameterType)).ToArray();
        var bf = BindingFlags.Public | BindingFlags.NonPublic | (mi.IsStatic ? BindingFlags.Static : BindingFlags.Instance);

        var expandMethodName = methodName ?? $"{origMethodName}Expander";
        var em = methodType.GetMethod(expandMethodName, bf, null, argTypes, null);
        if (em == null)
            throw new NullReferenceException($"Unable to find MethodInfo for {methodType.Name}.{expandMethodName}");
        else
            return em;
    }
}

Now, an IQueryable extension to trigger the expansion:

public static class IQueryableExt {
    private static object Evaluate(this Expression e) => (e is ConstantExpression c) ? c.Value : Expression.Lambda(e).Compile().DynamicInvoke();

    /// <summary>
    /// ExpressionVisitor to replace x.method("x..z") to methodexpander(x, "x..z")
    /// </summary>
    private class ExpandableMethodVisitor : ExpressionVisitor {
        public override Expression Visit(Expression node) {
            if (node?.NodeType == ExpressionType.Call) {
                var callnode = node as MethodCallExpression;
                var ema = callnode.Method.GetCustomAttribute<ExpandMethodAttribute>();
                if (ema != null)
                    return (Expression)ema.ExpandingMethod(callnode.Method).Invoke(callnode.Object, callnode.Arguments.Select((ae, n) => n == 0 ? ae : ae.Evaluate()).ToArray());
            }

            return base.Visit(node);
        }
    }

    private static T ExpandMethods<T>(this T orig) where T : Expression => (T)(new ExpandableMethodVisitor().Visit(orig));

    public static IQueryable<T> Expand<T>(this IQueryable<T> q) => q.Provider.CreateQuery<T>(q.Expression.ExpandMethods());
}

Finally, the specific extension needed to filter characters from a field expression:

public static class LINQExt {
    // body only for LINQ to Objects use
    [ExpandMethod("CleanUp")]
    public static string RemoveAll(this string src, string removeChars) => removeChars.Aggregate(src, (ans, ch) => ans.Replace(ch.ToString(), ""));

    private static Expression CleanUp(this Expression dbFn, string charsToRemove) {
        var toCharE = Expression.Constant(String.Empty);
        var replaceMI = typeof(string).GetMethod("Replace", new[] { typeof(string), typeof(string) });

        var methodBody = dbFn;
        foreach (var ch in charsToRemove)
            methodBody = Expression.Call(methodBody, replaceMI, Expression.Constant(ch.ToString()), toCharE);

        return methodBody;
    }    
}

Now you can use the RemoveAll extension in a query, and process the query with Expand before instantiating it.

So, for the example:

var filter = "filter";
var removals = "'\"#/-";

query = query.Where(x =>
                    x.Name.RemoveAll(removals).Contains(filter) ||
                    x.Full.RemoveAll(removals).Contains(filter))
              .Expand();

This could probably be added to LINQKit to be handled with their IQueryable/IProvider wrappers.

Answer 6

I think the trick here is to move the logic in to SQL, and build it out as a SQL function then call the SQL function from your LINQ query.

Something like this perhaps ...

https://stackoverflow.com/questions/20131632/calling-a-sql-user-defined-function-in-a-linq-query