Linq to Sql any keyword search query

Question

I have a case in my application where the user can search for a list of terms. The search needs to make three passes in the following order:

One for an exact match of what they entered. Done, easy.
One where all the words (individually) match. Done, also easy.
One where any of the words match...how?

Essentially, how do I, in Linq to Sql, tell it to do this:

select * from stuff s where s.Title like '%blah%' || s.Title like '%woo&' || s.Title like '%fghwgads%' || s.Title like...

And so on?

You should know that this kind of thing is really best handled by Full-Text Search. The LIKE '%xyz%' version will run, but the performance will suck. — Aaronaught
– Aaronaught, Commented Jan 6, 2010 at 4:38

Aaronaught · Accepted Answer · 2010-01-06 03:51:06Z

7

This might be a tough one... I think you'd have to write your own operator.

(Update: Yep, I tested it, it works.)

public static class QueryExtensions
{
    public static IQueryable<TEntity> LikeAny<TEntity>(
        this IQueryable<TEntity> query,
        Expression<Func<TEntity, string>> selector,
        IEnumerable<string> values)
    {
        if (selector == null)
        {
            throw new ArgumentNullException("selector");
        }
        if (values == null)
        {
            throw new ArgumentNullException("values");
        }
        if (!values.Any())
        {
            return query;
        }
        var p = selector.Parameters.Single();
        var conditions = values.Select(v =>
            (Expression)Expression.Call(typeof(SqlMethods), "Like", null,
                selector.Body, Expression.Constant("%" + v + "%")));
        var body = conditions.Aggregate((acc, c) => Expression.Or(acc, c));
        return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
    }
}

Then you could call this with:

string[] terms = new string[] { "blah", "woo", "fghwgads" };
var results = stuff.LikeAny(s => s.Title, terms);

P.S. You'll need to add the System.Linq.Expressions and System.Data.Linq.SqlClient namespaces to your namespaces for the QueryExtensions class.

edited Jan 6, 2010 at 3:51

answered Jan 6, 2010 at 3:38

Aaronaught

123k26 gold badges273 silver badges344 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juan Carlos Velez Over a year ago

I used the code in Linq to Entities, and I get this error: LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression.

Collectives™ on Stack Overflow

Linq to Sql any keyword search query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related