Dynamic library (System.Linq.Dynamic) SQL LIKE Operator

c# dynamic-linq linq sql

Question

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'

1
2
11/16/2018 11:52:39 AM

Popular Answer

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();
}
1
11/19/2018 2:50:16 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow