Hitting the 2100 parameter limit (SQL Server) when using Contains()

Question

from f in CUSTOMERS
where depts.Contains(f.DEPT_ID)
select f.NAME

depts is a list (IEnumerable<int>) of department ids

This query works fine until you pass a large list (say around 3000 dept ids) .. then I get this error:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

I changed my query to:

var dept_ids = string.Join(" ", depts.ToStringArray());
from f in CUSTOMERS
where dept_ids.IndexOf(Convert.ToString(f.DEPT_id)) != -1
select f.NAME

using IndexOf() fixed the error but made the query slow. Is there any other way to solve this? thanks so much.

How about [like so](http://stackoverflow.com/questions/567963/linq-expression-to-return-property-value/568771#568771) (which batches it into manageable pieces). The other (non-LINQ) options involve CSV and a "split" UDF, and table-valued-parameters (in SQL2008). — Marc Gravell, Mar 17 '09 at 21:56
Mark, can you please explain what is best alternative to `contain` if I have various parameters count from 1 to 2000? I know that this create bunch of plans in db, but it seems that usage of `like '%%'` will take even more db resource time. What should I use? — Johnny_D, Oct 15 '13 at 13:23
The 2100 parameter limit problem does not exist in Entity Framework: http://stackoverflow.com/questions/8898564/entity-framework-hitting-2100-parameter-limit — nmit026, Dec 01 '16 at 04:25
What about when search for `DEPT_id` when equals 1, will find any id has 1 in any digit! Am I missing something? — A.Akram, Sep 17 '17 at 18:14

ADM-IT · Answer 1 · 2021-06-28T16:01:29.397

17

My solution (Guids is a list of ids you would like to filter by):

List<MyTestEntity> result = new List<MyTestEntity>();
for(int i = 0; i < Math.Ceiling((double)Guids.Count / 2000); i++)
{
    var nextGuids = Guids.Skip(i * 2000).Take(2000);
    result.AddRange(db.Tests.Where(x => nextGuids.Contains(x.Id)));
}
this.DataContext = result;

edited Jun 28 '21 at 16:01

answered Feb 11 '14 at 17:43

ADM-IT

3,719
1
25
26

4

Rather than using Ceiling (and multiplying in Skip) and incrementing by 1, just use Count for the condition and increment by 2000. Also make it a constant to make it configurable. – webXL Oct 17 '14 at 00:00

Joel · Answer 2 · 2009-03-17T22:36:19.190

6

Why not write the query in sql and attach your entity?

It's been awhile since I worked in Linq, but here goes:

IQuery q = Session.CreateQuery(@"
         select * 
         from customerTable f
         where f.DEPT_id in (" + string.Join(",", depts.ToStringArray()) + ")");
q.AttachEntity(CUSTOMER);

Of course, you will need to protect against injection, but that shouldn't be too hard.

edited Mar 17 '09 at 22:36

answered Mar 17 '09 at 21:54

Joel

19,175
2
63
83

thanks joel. let me try it and i'll let you know how it goes. – ban-G Mar 17 '09 at 21:58
14

Caveat: that is OK with integers, but with strings: watch out for SQL injection. – Marc Gravell Mar 17 '09 at 22:06
1

Presumably you want a comma in there somewhere, Joel? – Marc Gravell Mar 17 '09 at 22:07
Yes, injection is always a concern when writing dynamic sql, but with integers you're safer. Comma added. ;) – Joel Mar 17 '09 at 22:37
hi joel, thanks again for taking time to answer my question. :-) – ban-G Mar 18 '09 at 22:48
@George, If you read to the end (or the comments), you'll notice I warned against the injection risk. – Joel May 03 '12 at 13:19
Ugly, but somehow a nice workaround :) Why is there a parameter number limit? – yonexbat Jul 03 '13 at 13:18
I'm pretty sure that if the statement gets large enough you will still hit a limit. I think it was 65k characters at one point? – jocull Dec 08 '16 at 21:27
This might generate terrible performance with non demo queries that contain joins and filters (and sometimes a complex query error) – Cesar Jan 22 '23 at 07:48

jpierson · Answer 3 · 2009-10-06T20:46:04.810

You will want to check out the LINQKit project since within there somewhere is a technique for batching up such statements to solve this issue. I believe the idea is to use the PredicateBuilder to break the local collection into smaller chuncks but I haven't reviewed the solution in detail because I've instead been looking for a more natural way to handle this.

Unfortunately it appears from Microsoft's response to my suggestion to fix this behavior that there are no plans set to have this addressed for .NET Framework 4.0 or even subsequent service packs.

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984

UPDATE:

I've opened up some discussion regarding whether this was going to be fixed for LINQ to SQL or the ADO.NET Entity Framework on the MSDN forums. Please see these posts for more information regarding these topics and to see the temporary workaround that I've come up with using XML and a SQL UDF.

Roman O · Answer 4 · 2019-04-02T10:17:23.943

1

I had similar problem, and I got two ways to fix it.

Intersect method
join on IDs

To get values that are NOT in list, I used Except method OR left join.

Update

EntityFramework 6.2 runs the following query successfully:

var employeeIDs = Enumerable.Range(3, 5000);
var orders =
    from order in Orders
    where employeeIDs.Contains((int)order.EmployeeID)
    select order;

edited Apr 02 '19 at 10:17

answered Nov 12 '12 at 14:05

Roman O

3,172
30
26

6

Could you give an example how to do it? – Philipp M Feb 10 '14 at 17:22
The limit is a server limit. What was the resulting query that didn't cause the error? – Cesar Jan 22 '23 at 07:50

score 0 · Answer 5 · answered Dec 01 '22 at 23:55

Your post was from a while ago, but perhaps someone will benefit from this. Entity Framework does a lot of query caching, every time you send in a different parameter count, that gets added to the cache. Using a "Contains" call will cause SQL to generate a clause like "WHERE x IN (@p1, @p2.... @pn)", and bloat the EF cache.

Recently I looked for a new way to handle this, and I found that you can create an entire table of data as a parameter. Here's how to do it:

First, you'll need to create a custom table type, so run this in SQL Server (in my case I called the custom type "TableId"):

        CREATE TYPE [dbo].[TableId] AS TABLE(
            Id[int] PRIMARY KEY
        )

Then, in C#, you can create a DataTable and load it into a structured parameter that matches the type. You can add as many data rows as you want:

        DataTable dt = new DataTable();
        dt.Columns.Add("id", typeof(int));

This is an arbitrary list of IDs to search on. You can make the list as large as you want:

        dt.Rows.Add(24262);
        dt.Rows.Add(24267);
        dt.Rows.Add(24264);

Create an SqlParameter using the custom table type and your data table:

        SqlParameter tableParameter = new SqlParameter("@id", SqlDbType.Structured);
        tableParameter.TypeName = "dbo.TableId";
        tableParameter.Value = dt;

Then you can call a bit of SQL from your context that joins your existing table to the values from your table parameter. This will give you all records that match your ID list:

        var items = context.Dailies.FromSqlRaw<Dailies>("SELECT * FROM dbo.Dailies d INNER JOIN @id id ON d.Daily_ID = id.id", tableParameter).AsNoTracking().ToList();

score -1 · Answer 6 · edited May 23 '17 at 12:00

-1

You could always partition your list of depts into smaller sets before you pass them as parameters to the IN statement generated by Linq. See here:

Divide a large IEnumerable into smaller IEnumerable of a fix amount of item

edited May 23 '17 at 12:00

Community

1
1

answered May 23 '15 at 15:45

btt

422
6
16

These solutions work for simple logic and cannot be expanded into an approach. The general case is that the list is a single parameter for a query that usually has other filters and must return a page. – Cesar Jan 22 '23 at 07:52

Hitting the 2100 parameter limit (SQL Server) when using Contains()

6 Answers6

Linked

Related