Selecting a Record Where a Text Column Contains a Key Word

[Aaron Drenberg]

Hi,

Suppose I have this example table.

[SampleTable]
Id : PK, uniqueidentifier
Text: varchar(3000)

I've got a search method that accepts a list of key words as a parameter. What I'd like to do is select any records from SampleTable where the Text column contains any of the key words. For example:

public IQueryable<SampleTable> Search(List<string> keywords)
{
        return = from s in ObjectScope.Extent<SampleTable>()
                      where keywords.Any(k => s.Text.Any(t => t.Contains(k)))
                      select s;
}

I'd like to see if I can accomplish this using your LINQ support. Any suggestions on how to get it done? Thanks!

[Aaron Drenberg]

Looks like I've got it figured out.

In my query, I was trying to be efficient by consolidating the search into one statement. Instead, I tried using a foreach loop. I turned on event tracing, and the resulting SQL statement is executed with one round trip, so everything is working fine.

public IQueryable<SampleTable> Search(List<string> keywords)
{
        var query = from s in ObjectScope.Extent<SampleTable>() select s;
        foreach (string keyword in keywords)
        {
                query = from s in query
                             where s.Text.Any(t => t.Contains(keyword))
                             select s;
        }
        return query;
}

[Petko_I]

Hi Aaron Drenberg,

We are glad to see that you have resolved the issue. If you feel you need to know something else, you are welcome to ask.

Regards,
Petko_I
the Telerik team