LINQ Where in collection clause

Question

I've been looking on google but not finding anything that does the trick for me.

as you know SQL has a "where x in (1,2,3)" clause which allows you to check against multiple values. I'm using linq but I can't seem to find a piece of syntax that does the same as the above statement.

I have a collection of category id's (List) against which I would like to check

I found something that uses the .contains method but it doesn't even build.

score 39 · Accepted Answer · answered Jun 10 '09 at 06:09

39

You have to use the Contains method on your id list:

var query = from t in db.Table
            where idList.Contains(t.Id)
            select t;

answered Jun 10 '09 at 06:09

Christian C. Salvadó

807,428
183
922
838

I have one follow up though. the column of id's is nullable (which I forgot and fixed by using the value property) what happens if the value is null? – Jan W. Jun 10 '09 at 06:22
1

If t.Id value is null then result won't include this record. – Jun 10 '09 at 06:47

score 19 · Answer 2 · answered Jun 10 '09 at 06:10

The syntax is below:

IEnumerable<int> categoryIds = yourListOfIds;

var categories = _dataContext.Categories.Where(c => categoryIds.Contains(c.CategoryId));

The key thing to note is that you do the contains on your list of ids - not on the object you would apply the in to if you were writing sql.

score 3 · Answer 3 · answered Jun 10 '09 at 06:08

3

Here's an article illustrating the approach. You should indeed use the Contains method over your collection which will be translated into IN clause.

answered Jun 10 '09 at 06:08

Darin Dimitrov

1,023,142
271
3,287
2,928

6

The linked article no longer exists. – MattD May 20 '16 at 16:06

score 1 · Answer 4 · answered Nov 07 '14 at 12:06

Here is my realization of WhereIn() Method, to filter IQueryable collection by a set of selected entities:

 public static IQueryable<T> WhereIn<T,TProp>(this IQueryable<T> source, Expression<Func<T,TProp>> memberExpr, IEnumerable<TProp> values) where T : class
    {
        Expression predicate = null;
        ParameterExpression param = Expression.Parameter(typeof(T), "t");

        bool IsFirst = true;

        MemberExpression me = (MemberExpression) memberExpr.Body;
        foreach (TProp val in values)
        {
            ConstantExpression ce = Expression.Constant(val);


            Expression comparison = Expression.Equal(me, ce);

            if (IsFirst)
            {
                predicate = comparison;
                IsFirst = false;
            }
            else
            {
                predicate = Expression.Or(predicate, comparison);
            }
        }

        return predicate != null
            ? source.Where(Expression.Lambda<Func<T, bool>>(predicate, param)).AsQueryable<T>()
            : source;
    }

And calling of this method looks like:

IQueryable<Product> q = context.Products.ToList();

var SelectedProducts = new List<Product>
{
  new Product{Id=23},
  new Product{Id=56}
};
...
// Collecting set of product id's    
var selectedProductsIds = SelectedProducts.Select(p => p.Id).ToList();

// Filtering products
q = q.WhereIn(c => c.Product.Id, selectedProductsIds);

LINQ Where in collection clause

4 Answers4

Linked