C# 如果包含不受支持，您如何在 LINQ to Entities（实体框架）中执行 SQL 样式的“IN”语句？

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

提问by JC Grubbs

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:

我正在使用 LINQ to Entities（不是 LINQ to SQL），但在创建“IN”样式查询时遇到问题。这是我目前的查询：

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.

LINQ to Entities 无法识别方法 'Boolean Contains(Int64)' 方法，并且此方法无法转换为存储表达式。

Does anyone have a workaround or another solution for this?

有没有人有解决方法或其他解决方案？

采纳答案by liggett78

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 thispost.

或动态构建 WHERE 部分，如本文所述。

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

PS - 信息已更新，此答案更新如下以保持相关性：

The link referenced contains the following update:

引用的链接包含以下更新：

...in EF4 we added support for theContains method and at least in thisspecific case for collection-valuedparameters. Therefore this kind ofcode now works right out of the boxand it is not necesary to use anyadditinal expression building method:

...在 EF4 中，我们添加了对Contains 方法的支持，至少在这种特定情况下添加了对集合值参数的支持。因此，这种代码现在开箱即用，不需要使用任何额外的表达式构建方法：

var statusesToFind = new List<int> {1, 2, 3, 4};
var foos = from foo in myEntities.Foos
           where statusesToFind.Contains(foo.Status)
           select foo;

回答by Stef Heyenrath

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

我的解决方法是将实体结果转换为列表，然后应用 Contains()。

Example:

例子：

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

回答by Drew Noakes

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

Any在某些情况下，您可以为此使用 Linq 的扩展方法：

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.

在这种情况下，生成的 SQL 看起来很奇怪，但与许多 Linq-to-Entities 生成的 SQL 一样，它对于人类来说可能过于冗长，但在实践中运行速度很快。

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

回答by VVS

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

正如 Diego B Vega 在这篇文章中提到的（第二个答案），Contains现在应该可以在 EF4 中工作。