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.

Creating query which requires more than 2100 statically defined items for `IN` statement (that is counter part of `Contains`) doesn't look like a correct approach. Such operation should be handled completely on DB without need for passing data from client. — Ladislav Mrnka, Jan 17 '12 at 17:18
I understand your concern, but there are certainly cases where *all* of the required data will not be in the database. There are definitely alternatives to passing such a large list of parameters, but this simple catch-all happens to be extremely flexible for how we would like to use it (and 99% of the time it won't need to use it). — Ocelot20, Jan 17 '12 at 17:32

score 23 · Accepted Answer · edited May 23 '17 at 11:53

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.

Cristian, thanks for the useful answer!! You wrote: "While this is extremely inefficient, it works and yields the expected result." Can you please explain why asking for 3000 items at once is extremely inefficient vs asking in chunks? — Liraz Shay, Sep 17 '20 at 08:13
@LirazShay I don't remember the specifics right now :), but I would advise running some tests for your specific case to see which approach is better. Anyway, if it's possible to have a very large list, I'd implement chunking to avoid cases when there are, say 1M values. — Cristian Lupascu, Sep 21 '20 at 07:23
This handles a very specific case and is not a generic approach that can be reused (when the query has both filters and needs to return the next page) — Cesar, Jan 22 '23 at 07:55

Entity Framework Hitting 2100 Parameter Limit

1 Answers1

Linked