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.

http://stackoverflow.com/questions/3024201/how-to-use-the-in-operator-in-linq — taher chhabrawala, Aug 14 '12 at 16:57

Andrew Hare · Accepted Answer · 2009-07-16T16:24:29.643

50

~~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:

edited Jul 16 '09 at 16:24

answered Jul 16 '09 at 14:19

Andrew Hare

344,730
71
640
635

1

Perhaps you can look into these workarounds: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/ – Andrew Hare Jul 16 '09 at 15:17
if i do like this for string values then EF generated query without parameters, it just put string values into query – Vladimir Kruglov Jan 23 '17 at 08:48
1

Looks like they have added support for "Contains". In the shared link, search for Zeeshan Hirani's post.. – user007 Dec 04 '17 at 22:03

score 22 · Answer 2 · answered Dec 29 '10 at 12:58

22

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

answered Dec 29 '10 at 12:58

eka808

2,257
3
29
41

5

This answer is correct for entity framework 4, which was release with .net 4 - EF 4 was extended to perform a WHERE IN in this case - not all the way there yet, but closer – Mark Mullin Mar 21 '11 at 14:08

score 7 · Answer 3 · answered Jul 16 '09 at 15:16

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

How to do an "in" query in entity framework?

3 Answers3

Linked

Related