LINQ extensions - Simplified keyword search

Recently, I came across interesting question at LINQ Forums (Dynamic conditions: How to achieve multiple "OR" conditions with LINQ? [1]). The question is whether LINQ (and especially LINQ to SQL) provides any simple way to return only records that contain one or more of specified keywords in the name. The question looks simple, but it is simple only if you know the number of keywords that you want to look for. In this case you can write following LINQ query:

  // Products that contain "kwd1" or "kwd2" in the name 

  var q = from p in db.Products
    where p.ProductName.Contains("kwd1") || p.ProductName.Contains("kwd2")
    select p;

The problem with previous code is that you can't use it if the list of keywords is dynamically entered by user (and so its length may vary). Of course, if you want to run query on in-memory data, you can get very nice results by writing extension method called ContainsAny that performs test for keyword array, but if you want to be able to translate query to SQL, the situation is a bit complicated.

Building expression tree

The first solution is to build the whole expression tree that is used for filtering (in the where clause) using Expression object. The code that builds expression tree is much longer, because it must generate expression for calling method Contains for every keyword in the array. The following example shows how to build test for only two keywords for simplicity:

// build as many conditions as you need
Expression t1 = Expression.CallVirtual(
   typeof(string).GetMethod("Contains"),
   Expression.Property(p, typeof(Product).GetProperty("ProductName")),
   new Expression[] { Expression.Constant("Chef") });
Expression t2 = Expression.CallVirtual(
   typeof(string).GetMethod("Contains"), 
   Expression.Property(p, typeof(Product).GetProperty("ProductName")),
   new Expression[] { Expression.Constant("Sir") });

// merge conditions using Expression.Or

var test = Expression.Lambda<Func<Product, bool>>
  (Expression.Or(t1, t2), new ParameterExpression[] { p });
var filter = Queryable.Where(db.Products, test);

// Execute the query

var q = from prod in filter
   select prod;

You might also think of using Queryable.Union method that returns union of results from several queries. Generating query using this method is possible and the code is less complicated, but the resulting SQL command generated by the LINQ to SQL is very poor and it would be obviously slower. That's definitely not an issue in LINQ, because the query created using Union is very far from natural query that you would normally use.

Better solution

The previous solution can be used for generating any expression tree, so there will be always certain situations, when this would be the only possible way, however I think that these two methods (ContainsAny and ContainsAll) will be used very often. I already wrote an extension to LINQ that allows you to 'call' expression tree in query [3], so I used this project and I added support for the following two methods:

using EeekSoft.Query;

// Select products, that conain one of values from array in their name

var q1 = from p in db.Products.ToExpandable()

    where p.ProductName.ContainsAny("Sir", "Chef")

    select new { p.ProductName };

// Select products, that conain all of values from array in their name

var q1 = from p in db.Products.ToExpandable()

    where p.ProductName.ContainsAll("Gu", "Ca")

    select new { p.ProductName };

To be able to write this code, you have to download the library EeekSoft.Query first. [See Downloads]

How does it work?

I already described the method that I used here for extending the LINQ in my previous article [3], so you can refer to it for more details. The important point is the call to the ToExpandable extension method. This method returns IQueryable wrapper around the original data source (data table in this case). When query is executed, this wrapper is called and it modifies the expression tree before it is passed to LINQ to SQL converter, so the methods like ContainsAny are replaced with some other expression that the LINQ to SQL can understand.

References and downloads

Discuss on twitter, .
Send corrections via GitHub pull requests.

Published: Friday, 28 July 2006, 3:57 AM
Tags: C# language