If Contains isn't supported, how can you make a SQL-style 'IN' query in LINQ to Entities (Entity Framework)?

c# entity-framework linq linq-to-entities

Question

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?

1
13
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:

...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;
8
5/10/2011 6:04:55 PM

Popular Answer

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


Related Questions





Related

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