How to filter query results where name contains any of the words being searched for?

Question

I'm trying to modify some code that returns results from the database. Currently the code is as follows, and matches whatever funds have a name like the search term.

        var ret = (from funds in queryable
                where
                    SqlMethods.Like(funds.Name, searchTerm)
                select
                    funds);

The problem with this is that it's too specific. If the search term is "pension growth" it'll only return results that have "pension growth" in the name. I'd like to change this to return all funds that have either "pension" or "growth" in the name (or whatever combination of search terms the user could include).

What I'd like to do is split the search term into its component parts and apply a SQL 'in' operator to the query like in ('pension', 'growth') or something, so that it'd return anything that had some of the terms, but that doesn't appear to be an option.

Could somebody please suggest another way that I could go about this? Thanks.

Answer 1

You can try to use the Contains() method on a list:

var myListOfWords = new List<string>{"pension","growth","42"};
var result = from funds in queryable
             where myListOfWords.Contains(funds.Name)
             select funds;