The Entity Framework has reached its 2100 parameter limit.

entity-framework expression-trees linq parameters sql

Question

I'm converting a portion of our LINQ-to-SQL code to Entity Framework. In the past, when I ran into the SQL Server limit of 2100 parameters (explained in here), I utilized the workaround suggested by Marc Gravell in here. It is incompatible with Entity Framework, as he acknowledged in his own answer.

I don't even know where to start with Expressions, but I'm looking for basically the same extension approach that works with Entity Framework. I appreciate any assistance you may provide in advance.

1
8
5/23/2017 12:17:05 PM

Accepted Answer

In EF, the issue with the 2100 parameter limit does not present.

I tested database of AdventureWorks with SQL Express 2008 R2 as follows: I'm attempting to place every product whereProductCategoryId is among the possible values (1, 2, 3).

The resulting SQL is made using LINQ.WHERE This is how a clause looks:

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 brings up the problem with the maximum parameter number), however with EF 4.0 it appears as follows:

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

Then, I examined this using EF for a set 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();
}

Despite being very wasteful, this does the job and produces the desired outcome.

Nevertheless, you may also utilize theInRange utilizing the Library LINQKit in addition to the extension sourced from Marc Gravell with EF, as follows:

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

(the AsExpandable LINQKit defines extension.)

According on the amount of items in the list and the size of the chunk, this may be much more efficient than the non-chunked alternative and yields the desired result (executes the query in pieces).

15
5/23/2017 11:53:21 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow