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

问题描述

我正在将一些代码从LINQ-to-SQL迁移到实体框架.以前，当使用SQL Server的2100参数限制运行时(描述这里.如他自己的答复所述，它不适用于Entity Framework.

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.

我对Expressions完全没有经验，无法知道从哪里开始，但是我要寻找的本质上是相同的扩展方法，但适用于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屋！