Linq to SQL how to do "where [column] in (list of values)"

Question

I have a function where I get a list of ids, and I need to return the a list matching a description that is associated with the id. E.g.:

public class CodeData {     string CodeId {get; set;}     string Description {get; set;} }  public List<CodeData> GetCodeDescriptionList(List<string> codeIDs)     //Given the list of institution codes, return a list of CodeData     //having the given CodeIds } 

So if I were creating the sql for this myself, I would simply do something like the following (where the in clause contains all the values in the codeIds argument):

Select CodeId, Description FROM CodeTable WHERE CodeId IN ('1a','2b','3') 

In Linq to Sql I can't seem to find the equivalent of the "IN" clause. The best I've found so far (which doesn't work) is:

 var foo = from codeData in channel.AsQueryable<CodeData>()            where codeData.CodeId == "1" || codeData.CodeId == "2"            select codeData; 

The problem being, that I can't dynamically generate a list of "OR" clauses for linq to sql, because they are set at compile time.

How does one accomplish a where clause that checks a column is in a dynamic list of values using Linq to Sql?

Answer 1

Use

where list.Contains(item.Property) 

Or in your case:

var foo = from codeData in channel.AsQueryable<CodeData>()           where codeIDs.Contains(codeData.CodeId)           select codeData; 

But you might as well do that in dot notation:

var foo = channel.AsQueryable<CodeData>()                  .Where(codeData => codeIDs.Contains(codeData.CodeId)); 

Answer 2

You could also use:

List<int> codes = new List<int>();  codes.add(1); codes.add(2);  var foo = from codeData in channel.AsQueryable<CodeData>()           where codes.Any(code => codeData.CodeID.Equals(code))           select codeData;