实体框架达到 2100 参数限制 [英] Entity Framework Hitting 2100 Parameter Limit

问题描述

我正在将我们的一些代码从 LINQ-to-SQL 迁移到实体框架.以前，当遇到来自 SQL Server 的 2100 参数限制时(描述 此处)，我使用了 Marc Gravell 这里.正如他在自己的回复中所述，它不适用于实体框架.

I'm migrating some of our code from LINQ-to-SQL to Entity Framework. Previously, when run up against a 2100 parameter limit from SQL Server (described here), I used the solution provided by Marc Gravell here. As stated in his own response, it does not work with Entity Framework.

我对表达式完全没有经验，不知道从哪里开始，但我正在寻找本质上是相同的扩展方法，但适用于实体框架.预先感谢您提供的任何帮助.

I'm entirely too inexperienced with Expressions to know where to begin, but what I'm looking for is essentially the same extension method but applicable to Entity Framework. Thanks in advance for any help you can provide.

推荐答案

EF中不存在2100参数限制问题.

The 2100 parameter limit problem does not exist in EF.

我对AdventureWorks 数据库(在 SQL Express 2008 R2 中)进行了测试):我正在尝试获取 ProductCategoryId 在值 (1, 2, 3) 范围内的所有产品.

I've run a test on the AdventureWorks database (in SQL Express 2008 R2): I'm trying to get all products where ProductCategoryId is in the range of values (1, 2, 3).

使用 LINQ，生成的 SQL WHERE 子句如下所示:

Using LINQ, the generated SQL WHERE clause looks like this:

WHERE [t0].[ProductCategoryID] IN (@p0, @p1, @p2)
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [3]

(这会导致最大参数数问题)，而在 EF 4.0 中，它看起来像这样:

(which leads to the max parameter number issue), whereas with EF 4.0 it looks like this:

WHERE [Extent1].[ProductCategoryID] IN (1,2,3)

接下来，我用 EF 测试了这个包含 3000 个值的列表:

Next, I've tested this with EF for a list of 3000 values:

var categoryList = Enumerable.Range(1, 3000).Select(i => (int?)i).ToArray();

using (var aw = new AdventureWorksEntities())
{
    var products = aw.Products
        .Where(p => categoryList.Contains(p.ProductCategoryID))
        .ToList();
}

虽然这非常低效，但它有效并产生了预期的结果.

While this is extremely inefficient, it works and yields the expected result.

但是，也可以使用 InRange 扩展由 Marc Gravell 提供EF，还使用 ​​LINQKit 库，如下所示:

However, it is also possible to use the InRange extension provided by Marc Gravell with EF, by also using the LINQKit library, like so:

using (var aw = new AdventureWorksEntities())
{
    var products = aw.Products
        .AsExpandable()
        .InRange(p => p.ProductCategoryID, 1000, categoryList)
        .ToList();
}

(AsExpandable 扩展在 LINQKit 中定义)

(the AsExpandable extension is defined in LINQKit)

这会产生预期的结果(以块为单位执行查询)，并且根据列表中的项目数量和块的大小，这可能比非块解决方案更有效.

This produces the expected result (executes the query in chunks) and, depending on the number of items in the list and the size of the chunk can be a lot more efficient than the non-chunked solution.

这篇关于实体框架达到 2100 参数限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！