sql - Entity Framework Hitting 2100 Parameter Limit

Question

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.

Answer 1

The 2100 parameter limit problem does not exist in EF.

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

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]

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

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

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.

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();
}

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