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>).
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).
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.
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.
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.
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.
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.
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.
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!
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.
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.
Subscribe to be the first to get our expert-written articles and tutorials for developers!