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 having problems making a "IN" style query since I'm using LINQ to Entities rather than LINQ to SQL. Here is my current remark:

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

However, when I do this, the following exception is raised:

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

Has anybody come up with a workaround or other remedy for this?

1
13
12/15/2010 8:55:12 PM

Accepted Answer

You must either employ this one:

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

can dynamically create the WHERE portion, like in the this post.

P.S. - To retain its currency, this response has been amended as follows:

The following update is included in the mentioned link:

...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 may use Linq'sAny Occasionally, an extension technique for this is:

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

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

In this situation, the produced SQL seems somewhat weird, yet, like other Linq-to-Entities generated SQL, it executes quickly in reality while maybe being unnecessarily lengthy for a human to read.

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