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
                .Include("Kind")
                .Include("PropertyValues")
                .Include("PropertyValues.KindProperty")
                .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?

Answers


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:

...in 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;

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.

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

As mentioned by Diego B Vega in this post (second answer), Contains should now work in EF4.


My workaround is to convert the entities result to a List and after that apply the Contains().

Example:

var items = db.InventoryItem
                .Include("Kind")
                .Include("PropertyValues")
                .Include("PropertyValues.KindProperty")
                .ToList()
                .Where(itm => valueIds.Contains(itm.ID));

Need Your Help

How to return a string literal from a function

c++ string literals return-type

I am always confused about return a string literal or a string from a function. I was told that there might be memory leak because you don't know when the memory will be deleted?

Use VIM omnicomplete for javascript with ctags

javascript vim ctags omnicomplete

I am using vim/gvim for 4 months already and now I found a way to use it's strengths.