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

Nathan_24954 0
Viewed 145792

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?

linq-to-sql linq
6 Answers

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));
user_226560

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;
user_1318480

Here is how I do it by using HashSet

        HashSet<String> hs = new HashSet<string>(new String[] { "Pluto", "Earth", "Neptune" });
        String[] arr =
        {
            "Pluto",
            "Earth",
            "Neptune",
            "Jupiter",
            "Saturn",
            "Mercury",
            "Pluto",
            "Earth",
            "Neptune",
            "Jupiter",
            "Saturn",
            "Mercury",
            // etc.
        };
        ICollection<String> coll = arr;

        String[] arrStrFiltered = coll.Where(str => hs.Contains(str)).ToArray();

HashSet is basically almost to O(1) so your complexity remains O(n).

user_47499450

No Entity Framework Net Core 2

string[] names = stringsTest.name.Split('|');

if (names.Length > 1)
{
    query = query.Where(w => names.Contains(w.name));
}
else
{
    query = query.Where(w => w.name== stringsTest.name);
}

var listEntity = await query.ToListDtoAsync(stringsTest);
user_108838760
Related