9
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ Please remove the second snippet, it's not a working code but a pseudocode. Your question could get closed for this. I'm pretty sure you'll get a solution that works similar to what you suggest. \$\endgroup\$ – t3chb0t Jan 10 '17 at 21:00
  • 3
    \$\begingroup\$ @t3chb0t It's clear that part isn't part of the existing code, so it should be fine. \$\endgroup\$ – Mast Jan 10 '17 at 21:02
  • \$\begingroup\$ It'd be great if someone could show how to write a custom query-provider for this case :-) I unfortuantelly cannot do it (yet). \$\endgroup\$ – t3chb0t Jan 10 '17 at 21:20
  • \$\begingroup\$ @t3chb0t writing a custom query provider is lots, lots of fun :-) \$\endgroup\$ – Mathieu Guindon Jan 11 '17 at 15:05
  • 1
    \$\begingroup\$ @t3chb0t I'm not sure I get it either =) \$\endgroup\$ – Mathieu Guindon Jan 11 '17 at 15:22
3
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ A single for loop is fine too as they should be in the same order anyway, I assume .Zip will be more costly. \$\endgroup\$ – Denis Jan 10 '17 at 21:13
  • 1
    \$\begingroup\$ This won't work bc it's LINQ to SQL, so the "RemoveAll" method won't be recognized -- that's why ".Replace" is being used (it translated in LINQ to SQL). This will generate something like: "Additional information: LINQ to Entities does not recognize the method 'System.String RemoveAll" \$\endgroup\$ – dochoffiday Jan 10 '17 at 21:15
  • \$\begingroup\$ @DocHoffiday Just remove the part that makes it extension method wouldn't that work ? I can provide example if you need one. \$\endgroup\$ – Denis Jan 10 '17 at 21:16
  • \$\begingroup\$ @denis - it's not just extension methods, any custom method will throw the "LINQ to Entities does not recognize the method" error. \$\endgroup\$ – dochoffiday Jan 10 '17 at 21:18
  • 1
    \$\begingroup\$ It would work if you do query.AsEnumerable().Where(..) but this will get everything from the server and filter it on the client side. Probably not so optimal. \$\endgroup\$ – t3chb0t Jan 10 '17 at 21:30
2
\$\begingroup\$
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.

| improve this answer | |
\$\endgroup\$
1
\$\begingroup\$

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

| improve this answer | |
\$\endgroup\$
  • 1
    \$\begingroup\$ This won't work bc it's LINQ to SQL, so the "RemoveAll" method won't be recognized -- that's why ".Replace" is being used (it translated in LINQ to SQL). This will generate something like: "Additional information: LINQ to Entities does not recognize the method 'System.String RemoveAll" \$\endgroup\$ – dochoffiday Jan 10 '17 at 21:15
1
\$\begingroup\$

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();
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ You should change the Where condition to .Where(x => x.Field1.Contains(filter) || x.Field2.Contains(filter)) otherwise you could get different results. Nevertheless +1 \$\endgroup\$ – Heslacher Jan 26 '17 at 7:08
0
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
0
\$\begingroup\$

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

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.