What is the linq equivalent to the SQL IN operator

Question

With linq I have to check if a value of a row is present in an array.
The equivalent of the sql query:

WHERE ID IN (2,3,4,5)

How can I do it?

Emmaemmalee

Answer 1

.Contains

var resultset = from x in collection where new[] {2,3,4,5}.Contains(x) select x

Of course, with your simple problem, you could have something like:

var resultset = from x in collection where x >= 2 && x <= 5 select x

Eva

I'm getting the error: int[] does not contain a definition of contains. – Imperfect

Could be because System.Linq not included ? – Gerous

Answer 2

Perform the equivalent of an SQL IN with IEnumerable.Contains().

var idlist = new int[] { 2, 3, 4, 5 };

var result = from x in source
          where idlist.Contains(x.Id)
          select x;

Spree

Answer 3

db.SomeTable.Where(x => new[] {2,3,4,5}.Contains(x));

or

from x in db.SomeTable
where new[] {2,3,4,5}.Contains(x)

Cede

shouldn't it be using x.<propertyname> that matches with the datatype to search, in this case, integers. like db.sometable.where(x => new[] {1,2}.Contains(x.Id)); – Wadley

Answer 4

Intersect and Except are a little more concise and will probably be a bit faster too.

IN

collection.Intersect(new[] {2,3,4,5});

NOT IN

collection.Except(new[] {2,3,4,5});

or

Method syntax for IN

collection.Where(x => new[] {2,3,4,5}.Contains(x));

and NOT IN

collection.Where(x => !(new[] {2,3,4,5}.Contains(x)));

Radloff

Answer 5

An IEnumerable<T>.Contains(T) statement should do what you're looking for.

Pah

Answer 6

A very basic example using .Contains()

List<int> list = new List<int>();
for (int k = 1; k < 10; k++)
{
    list.Add(k);
}

int[] conditionList = new int[]{2,3,4};

var a = (from test in list
         where conditionList.Contains(test)
         select test);

Muddlehead

You could clean this up a lot by changing your for loop to IEnumerable<int> list = Enumerable.Range(1, 10); – Cede

Point taken. +1 But to be fair, I was just writing code I knew would work without needing to test it :) – Muddlehead

Answer 7

The above situations work when the Contains function is used against primitives, but what if you are dealing with objects (e.g. myListOrArrayOfObjs.Contains(efObj))?

I found a solution! Convert your efObj into a string, thats separated by _ for each field (you can almost think of it as a CSV representation of your obj)

An example of such may look like this:

     var reqAssetsDataStringRep = new List<string>();

        foreach (var ra in onDemandQueueJobRequest.RequestedAssets)
        {
            reqAssetsDataStringRep.Add(ra.RequestedAssetId + "_" + ra.ImageId);
        }

        var requestedAssets = await (from reqAsset in DbContext.RequestedAssets
                                     join image in DbContext.Images on reqAsset.ImageId equals image.Id
                                     where reqAssetsDataStringRep.Contains(reqAsset.Id + "_" + image.Id)
                                     select reqAsset
                                           ).ToListAsync();

Countermove

Answer 8

You can write help-method:

    public bool Contains(int x, params int[] set) {
        return set.Contains(x);
    }

and use short code:

    var resultset = from x in collection
                    where Contains(x, 2, 3, 4, 5)
                    select x;

Verge

Answer 9

Following is a generic extension method that can be used to search a value within a list of values:

public static bool In<T>(this T searchValue, params T[] valuesToSearch)
{
    if (valuesToSearch == null)
        return false;
    for (int i = 0; i < valuesToSearch.Length; i++)
        if (searchValue.Equals(valuesToSearch[i]))
            return true;

    return false;
}

This can be used as:

int i = 5;
i.In(45, 44, 5, 234); // Returns true

string s = "test";
s.In("aa", "b", "c"); // Returns false

This is handy in conditional statements.

Cadwell