Linq: Looking for an efficient way to search a column to see if it contains any word from a list of words.

Question

I have a Linq query that will search a column for a word and return the number of entries found with the said word. I then loop this for each word I'm looking for.

var results = new List<WordCountResult>(words.Count);

foreach (var word in words)
{
   var wordCount = (from s in _searchResult
                       where s.Date>= startDate
                          && s.Date<= endDate
                          && SqlMethods.Like(s.TargetColumn, "%" + word + "%")
                    select s).Count();

   results.Add(new WordCountResult(word, wordCount));
}

return results;

While the code is neat it is inefficient as it query's the database multiple times.

Is the a Linq guru out there that can show how this can be done with one call the the database?

Answer 1

  var wordCount = (from s in _searchResult
                   where s.Date>= startDate
                      && s.Date<= endDate
                      && words.Contains(s.TargetColumn)
                select s).Count()

If you dont want to use exact string matching you can roll your own solution to modify the expresion tree to make it look like:

  var wordCount = (from s in _searchResult
               where s.Date>= startDate
                  && s.Date<= endDate
                  && (SqlMethods.Like(s.TargetColumn, "%" + words[0] + "%") ||SqlMethods.Like(s.TargetColumn, "%" + words[1] + "%")
                select s).Count()

etc.

PredicataBuilder shows you how to do this.