String Matching in LINQ

Performing simple string equality tests is often not enough when application user convenience is key. Often only a fragment of a string is known to the user, or many of them. Sometimes there is a need to search for a fragment in multiple columns.

Which options does OpenAccess and LINQ provide to perform string matching? Into which SQL is the LINQ expression translated?
Let’s use a simple class model for this post: a Person with a FirstName, a LastName and a NickName. Let’s assume parameters with the name simpleParam (string) and manyParam (ICollection<string>).

String equality comparison

Using the standard Equals method translates directly into the SQL = operator. The simple string parameter is translated directly into a SQL string parameter. The strings are compared in full length using the string collation rules of the database.

Also, the standard == operator can be used instead of String:Equals(string).

Case insensitive string equality comparison

Use the String:Compare(string,string,bool) method to explicitly express a case-insensitive complete string equality operation. The generated SQL will not depend on the case-sensitivity of the current database collation.

Alternatively, the case difference can be ignored manually. Beware, that the rules for case translation can be different on client and server; parameter values are handled on the client and therefore no SQL code is produced to perform case translation for those.

Using our SQL extension method this query can be expressed too. Use this extension with care as it can be database specific. See below for more use cases.

Fragment matching

When you need to match a fragment of a string with database values, you can use the String:Contains(string), String:StartsWith(string) and String:EndsWith(string)methods. All these methods will translate to SQL LIKE operations.

Wildcard comparison

The SQL LIKE operator can also be used directly from LINQ by calling Telerik.OpenAccess.ExtensionMethods:Matches(this string src, string pattern) or System.Data.Linq.SqlClient.SqlMethods:Like(string src, string pattern). The former translates the * and ? wildcards into % and _, the latter doesn’t do such a translation.

All the options mentioned above concentrate on the situation where a single field is matched against a single string value. However, often we have the situation that many strings need to be matched when the application wants to search for ‘Joe’ or ‘John’ or ‘Jack’. Let’s see how this can be achieved.

String equality comparison using many parameters

When many string parameter values must be matched fully against a single column, the SQL IN operator can be used. To generate this operator, a parameter of type ICollection<string> can be used (manyParam) with the ICollection<string>.Contains(string) method. Please note, that OpenAccess translates an empty collection to an SQL expression that will evaluate to false. Also notice, that different database systems have different limits on the number of values in an IN statement, usually < 100 is fine.

Unfortunately, there are situations when many fragments must be matched against a single column. There is no SQL operator that would directly allow us this to do. But LINQ expressions can be combined and an extension method can be defined that performs this combination.

String fragment matching using many parameters

When a query of type ‘match any of the given strings against one column’ is to be performed, the following extension method (given below) can be used. Beware that the logic is slightly different here: When no strings are given, there is no filtering performed.

Notice that LIKE operations can be expensive to compute on the database as all candidate string values need to be matched due to the prepended wildcard.

public static IQueryable<T> MultiValueContainsAny<T>(this IQueryable<T> source, ICollection<string> searchKeys, Expression<Func<T, string>> fieldSelector)
{
 if (source == null)
 throw new ArgumentNullException("source");
 if (fieldSelector == null)
 throw new ArgumentNullException("fieldSelector");
 if (searchKeys == null || searchKeys.Count == 0)
 return source;

    MethodInfo containsMethod = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
    Expression expression = null;
 foreach (var searchKeyPart in searchKeys)
    {
        Tuple<string> tmp = new Tuple<string>(searchKeyPart);
        Expression searchKeyExpression = Expression.Property(Expression.Constant(tmp), tmp.GetType().GetProperty("Item1"));
        Expression callContainsMethod = Expression.Call(fieldSelector.Body, containsMethod, searchKeyExpression);

 if (expression == null)
            expression = callContainsMethod;
 else
            expression = Expression.OrElse(expression, callContainsMethod);
    }
 return source.Where(Expression.Lambda<Func<T, bool>>(expression, fieldSelector.Parameters));
}

The implementation of the extension method just evaluates the passed string collection with the passed lambda expression so that the String:Contains(string) method is used, which translates to a SQL LIKE operation. You can alter the code so that other operations are executed instead; e.g. String:StartsWith(string).

Since we have gotten the taste of the combinability of LINQ expressions, let’s go even one step further and define a multi value / multi field string fragment matching function.

String fragment matching using many parameters against many fields

When a query like ‘Find me all Persons that have Jonny or Jackie as their first, last or nick name’ is to be performed, the following extension method (defined below) can be used. Please note that performing such a query can easily become a performance hog; use it’s power wisely!

public static IQueryable<T> MultiValueContainsAnyAll<T>(this IQueryable<T> source, ICollection<string> searchKeys, bool all, Expression<Func<T, string[]>> fieldSelectors)
{
 if (source == null)
 throw new ArgumentNullException("source");
 if (fieldSelectors == null)
 throw new ArgumentNullException("fieldSelectors");
    NewArrayExpression newArray = fieldSelectors.Body as NewArrayExpression;
 if (newArray == null)
 throw new ArgumentOutOfRangeException("fieldSelectors", fieldSelectors, "You need to use fieldSelectors similar to 'x => new string [] { x.LastName, x.FirstName, x.NickName }'; other forms not handled.");
 if (newArray.Expressions.Count == 0)
 throw new ArgumentException("No field selected.");
 if (searchKeys == null || searchKeys.Count == 0)
 return source;            

    MethodInfo containsMethod = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
    Expression expression = null;

 foreach (var searchKeyPart in searchKeys)
    {
        Tuple<string> tmp = new Tuple<string>(searchKeyPart);
        Expression searchKeyExpression = Expression.Property(Expression.Constant(tmp), tmp.GetType().GetProperty("Item1"));

        Expression oneValueExpression = null;
 foreach (var fieldSelector in newArray.Expressions)
        {
            Expression act = Expression.Call(fieldSelector, containsMethod, searchKeyExpression);
 if (oneValueExpression == null)
                oneValueExpression = act;
 else
                oneValueExpression = Expression.OrElse(oneValueExpression, act); 
        }

 if (expression == null)
            expression = oneValueExpression;
 else if (all)
            expression = Expression.AndAlso(expression, oneValueExpression);
 else
            expression = Expression.OrElse(expression, oneValueExpression);
    }
 return source.Where(Expression.Lambda<Func<T, bool>>(expression, fieldSelectors.Parameters));
} 

The extension method can operate in two modes: One allows to express that all values must be matched in any of the given fields, the other mode just requires that any of the values must be matching in any of the fields. Beware of the empty string collection parameter case: it is not filtering at all.
It is currently not possible to express that all of the values must be matched at most one time.

String filtering with fulltext operations

The most powerful of the string matching operations are often hidden in the database specific fulltext operations. There is not direct translation from a LINQ expression to such an operation; no API method is mapped to the generation of the needed SQL. However, using the SQL extension method (Telerik.OpenAccess.ExtensionMethods:SQL) one is able to provide OpenAccess with a database specific SQL fragment that the runtime will happily use in the SQL generated by a LINQ expression.
The same approach can also be used to perform similar string matching operations like phonetic search or regular pattern matching.
The use of such database specific operations can require the presence of components and indexes.
For more information on this topic see this blog post.