How do you do a SQL style 'IN' statement in LINQ to Entities (Entity Framework) if Contains isn't supported?


I'm using LINQ to Entities (not LINQ to SQL) and I'm having trouble creating an 'IN' style query. Here is my query at the moment:

var items = db.InventoryItem
                .Where(itm => valueIds.Contains(itm.ID)).ToList<InventoryItem>();

When I do this however, the following exception is thrown:

LINQ to Entities does not recognize the method 'Boolean Contains(Int64)' method, and this method cannot be translated into a store expression.

Does anyone have a workaround or another solution for this?

12/15/2010 8:55:12 PM

Accepted Answer

You need to either use this one:

.Where(string.Format("it.ID in {0}", string.Join(",", valueIds.ToArray())));

or construct the WHERE part dynamically, as in this post.

P.S. - Information has been updated and this answer updated as follows to maintain relevance:

The link referenced contains the following update: EF4 we added support for the Contains method and at least in this specific case for collection-valued parameters. Therefore this kind of code now works right out of the box and it is not necesary to use any additinal expression building method:

var statusesToFind = new List<int> {1, 2, 3, 4};
var foos = from foo in myEntities.Foos
           where statusesToFind.Contains(foo.Status)
           select foo;
5/10/2011 6:04:55 PM

You can use Linq's Any extension method for this in some cases:

var userIds = new[] { 1, 2, 3 };

from u in Users
     where userIds.Any(i => i==u.Id)
     select u;

The generated SQL looks pretty strange in such a case, but like much Linq-to-Entities generated SQL it might be overly verbose for a human, but runs fast in practice.

[Extent1].[Id] AS [Id], 
[Extent1].[DisplayName] AS [DisplayName], 
FROM [dbo].[Users] AS [Extent1]
    1 AS [C1]
        [UnionAll1].[C1] AS [C1]
        FROM  (SELECT 
            1 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        UNION ALL
            2 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
        3 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
    WHERE [UnionAll2].[C1] = [Extent1].[Id]

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow