"Where In" with linq to sql

Question

I am trying to provide the user with a list of items in a database table upon entering keywords in a textbox. The problem i currently have is that the user can enter several keyword and i would like to be able to perform only one roundtrip to the database and get back results matchings several keywords (an OR operation). Is there a way to perform such a task without having to hit the database for every single keyword?

Answer 1

I have found this page very useful when trying to figure out LINQ stuff in C#. It suggests the following syntax:

var results = from Item i in myCollectionOfItems
              where i.OneThing = aGoodThing || i.SomeStuff != theBadStuff
              select i;

EDIT: Sorry, misunderstood the question - I have updated to match the request for an OR operation. (I couldn't find the || syntax on the reference page, but I assume it will work. It compiles on my machine...

Answer 2

Contains() is your friend.

List<string> keywords = new List<string>();
keywords.Add("foo");
keywords.Add("bar");

var results = from item in db.Table
           where keywords.Contains(item.Description)
           select item;

...gives you

WHERE [t0].[Description] IN (@p0, @p1)

Answer 3

tokenize your output for your keyword searches.

You can then just keep saying or in c# ( the || operator) in the where condition.

i.e

var query = from row in mytable
            where (row.column == a) || (row.column == b) || //.... etc
            select row

Answer 4

You could try that :

List<string> keywords = new List<string>();
keywords.Add("foo");
keywords.Add("bar");

var results = from item in db.Table
              where keywords.Exists(k => item.Description.Contains(k))
              select item;

But I'm not sure it will convert to SQL...

Answer 5

I found a post that offer a pretty good solution to what I was trying to achieve. Thank you for your help!