Find values where a word is repeated

Question

Is there a way in LINQ to Entities (SQL) to identify all records where a specified word is repeated at least/less than a specified number of times?

I can do this in memory, looking for at least 3 instances of the word "word" using:

Where(Function(x) x.Description.Split("word").Count > 3)

However the Split() function cannot translate to a SQL equivalent, and so this can only be executed in-memory, which is excruciatingly slow by the time any number of records are involved.

The SQL to do this would be something like

WHERE Description LIKE '%word%word%word%'

And that's as far as I get - I can't work out how to get that SQL generated by LINQ to Entities. I tried the ugly hacky .Where(Function(x) x.Description.Contains("word%word%word") on the off chance, but I'm almost relieved that it doesn't work!

Answer 1

Linq2SQL

.Where (c => SqlMethods.Like(c.name, "word%word%word"));

Linq2Entities
See: http://jendaperl.blogspot.com/2011/02/like-in-linq-to-entities.html
and http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/6529a35b-6629-44fb-8ea4-3a44d232d6b9/

.Where("it.Name LIKE @searchTerm", 
    new ObjectParameter("searchTerm", "word%word%word"));