2

Someone has posted a similar question here How Dynamic library (System.Linq.Dynamic) support LIKE Operator?

But it's not exactly what I want. The Contains operator mentioned in that post only do this in SQL "%SOMETHING%". But the LIKE operator in SQL can do this "SOME%THING". Is there a similar operator for Dynamic LINQ? If not, is there a solution for this? Maybe with Regex? Also is there a single character wildcard? E.g. "SOM$THING" returns "SOMETHING" or "SOM3THING"

Edit: I am developing a WPF application which should read log files in XML format. Each element contains 34 fields. So instead of writing a very long WHERE, I have used System.Reflection.PropertyInfo to iterate each field to write the query and then use System.Linq.Dynamic to execute it.

Edit2: I have edited the code so it's more readable for the viewers.

Here is some code:

Example 1: prop.Name = "FirstName", paramterString = "Ke%in", returns "Kevin", "Kelvin"... Example 2: prop.Name = "FirstName", paramterString = "Ke$in", returns "Kevin", "Kelin"...

var query = "";
StringBuilder sb = new StringBuilder();
foreach (var prop in stringProps)
{
    sb.Append($"({prop.Name} != null And {prop.Name}.Contains({parameterString})");
}
query = sb.ToString().Substring(0, sb.Length - 4);
filteredData = filteredData.Where(query);

One of the requirement is to implement a SQL LIKE operator, so the users can use something like this to get the result they want: FirstName LIKE 'SOME%THING'

Kevin Man
  • 83
  • 1
  • 14
  • Can you restrict your project to MS SQL Server? Then you could [extend](https://stackoverflow.com/a/26450835/2557128) Dynamic LINQ to support `SQLMethods.Like`. – NetMage Nov 16 '18 at 18:56
  • Unfortunately this is not possible, because the data are imported by reading XML files. – Kevin Man Nov 19 '18 at 09:12
  • 1
    On the post you linked, there's an [answer](https://stackoverflow.com/a/4600035/3002584) suggesting to use a combination of `Contains`, `StartsWits`, `EndsWith`. Can this work for you? You can also use a Regex-Like, as shown [here](https://stackoverflow.com/questions/5417070/c-sharp-version-of-sql-like). But a pure `LIKE` is something that LInq doesn't provide. – OfirD Nov 19 '18 at 12:08
  • That's what I am afraid of. So I have used String.IndexOf() to create my own LIKE operator. If someone can review the code or even better, optimize it, I would be appreciate it. The code is posted in the answer. – Kevin Man Nov 19 '18 at 14:09
  • It begins to sound like this has nothing to do with SQL. If you just want to implement a `LIKE` style operator for LINQ to Objects, I would suggest translating SQL `LIKE` patterns to RE, and using the C# Regular Expression handling. – NetMage Nov 19 '18 at 19:48
  • Well, I have included the SQL tag because apparently the LIKE operator only exists in SQL or at least C# does not support it out of the box. I have looked into regular expression, but I have zero experience with RE and I don't have time to study it now. I am WAY behind schedule because of this wildcard thing. – Kevin Man Nov 20 '18 at 13:15

2 Answers2

1

Since there is no LIKE operator in Dynamic Linq library, I have created it my own version. The code is not pretty, but it does work. Hopefully someone can optimize it or suggest a better way to do this.

public string ParseWildcardInParameterString(string parameter, string propertyName)
{
    string stringWithWildcard = parameter;
    if (parameter.Contains("%") || parameter.Contains("$"))
    {
        stringWithWildcard = parameter;
        string[] substrings = parameter.Split(new Char[] { '%', '$' }, StringSplitOptions.RemoveEmptyEntries);
        string[] wildcards = ParseWildcards(parameter);
        if (substrings.Any())
        {
            StringBuilder sb = new StringBuilder();
            int substringsCount = substrings.Length;
            for (int i = 0; i < substringsCount; i++)
            {
                if (!substrings[i].EndsWith("\\"))
                {
                    int index = parameter.IndexOf(substrings[i]);
                    if (i < substringsCount - 1)
                    {
                        index = parameter.IndexOf(substrings[i + 1], index + 1);
                        if (index > -1)
                        {
                            string secondPart = wildcards[i].Equals("%") ?
                                $"{propertyName}.IndexOf(\"{substrings[i + 1]}\", {propertyName}.IndexOf(\"{substrings[i]}\") + \"{substrings[i]}\".Length) > -1" :
                                $"{propertyName}.IndexOf(\"{substrings[i + 1]}\", {propertyName}.IndexOf(\"{substrings[i]}\") + \"{substrings[i]}\".Length + 1) == {propertyName}.IndexOf(\"{substrings[i]}\") + \"{substrings[i]}\".Length + 1";
                            sb.Append($"({propertyName}.IndexOf(\"{substrings[i]}\") > -1 And {secondPart}) And ");
                        }
                    }
                }
            }
            stringWithWildcard = sb.Remove(sb.Length - 5, 5).Append(") Or ").ToString();
        }
    }
    return stringWithWildcard;
}

private string[] ParseWildcards(string parameter)
{
    IList<string> wildcards = new List<string>();
    foreach (var chararcter in parameter.ToCharArray())
    {
        if (chararcter.Equals('%') || chararcter.Equals('$'))
        {
            wildcards.Add(chararcter.ToString());
        }
    }
    return wildcards.ToArray();
}
Kevin Man
  • 83
  • 1
  • 14
0

Can you try if the Like functionality in System.Linq.Dynamic.Core does work for you?

Code example would be:

var dynamicFunctionsLike1 = context.Cars.Where(config, "DynamicFunctions.Like(Brand, \"%a%\")");

For full example, see ConsoleAppEF2.1.1/Program.cs

Stef Heyenrath
  • 9,335
  • 12
  • 66
  • 121