How to do an “in” query in entity framework?

Question

How can I do a select in linq to entities to select rows with keys from a list? Something like this:

var orderKeys = new int[] { 1, 12, 306, 284, 50047};
var orders = (from order in context.Orders 
              where (order.Key in orderKeys) 
              select order).ToList();
Assert.AreEqual(orderKeys.Count, orders.Count);

I tried using the Contains method as mentioned in some of the answers but it does not work and throws this exception:

LINQ to Entities does not recognize the method 'Boolean Contains[Int32](System.Collections.Generic.IEnumerable`1[System.Int32], Int32)' method, and this method cannot be translated into a store expression.

Answer 1

Try this:

var orderKeys = new int[] { 1, 12, 306, 284, 50047};
var orders = (from order in context.Orders 
              where orderKeys.Contains(order.Key);
              select order).ToList();
Assert.AreEqual(orderKeys.Count, orders.Count);

Edit: I have found some workarounds for this issue - please see WHERE IN clause?:

The Entity Framework does not currently support collection-valued parameters ('statusesToFind' in your example). To work around this restriction, you can manually construct an expression given a sequence of values using the following utility method:

Answer 2

I had the same problem and i solved like this

var orderKeys = new int[] { 1, 12, 306, 284, 50047};
var orders = (from order in context.Orders 
              where (orderKeys.Contains(order.Key)) 
              select order).ToList();
Assert.AreEqual(orderKeys.Count, orders.Count);

Answer 3

Unfortunately the EF can't translate the queries others have suggested. So while those queries would work in LINQ to Objects, they won't work in LINQ to Entities.

So the solution is a little more involved.

However I have a blog post on this exact topic here. Essentially the solution is to use a little expression tree magic to build an big OR expression.

Hope this helps

Alex