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.

Estell

How about like so (which batches it into manageable pieces). The other (non-LINQ) options involve CSV and a "split" UDF, and table-valued-parameters (in SQL2008). – Religious

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 <input parameter> '%<search field>%' will take even more db resource time. What should I use? – Dahlberg

The 2100 parameter limit problem does not exist in Entity Framework: #8899064 – Parmenides

did you tried any solution? not marked none – Mattern

What about when search for DEPT_id when equals 1, will find any id has 1 in any digit! Am I missing something? – Mesencephalon

Answer 1

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;

Pinfeather

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. – Carthage

Answer 2

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.

Endodermis

thanks joel. let me try it and i'll let you know how it goes. – Estell

Caveat: that is OK with integers, but with strings: watch out for SQL injection. – Religious

Presumably you want a comma in there somewhere, Joel? – Religious

Yes, injection is always a concern when writing dynamic sql, but with integers you're safer. Comma added. ;) – Endodermis

hi joel, thanks again for taking time to answer my question. :-) – Estell

@George, If you read to the end (or the comments), you'll notice I warned against the injection risk. – Endodermis

Ugly, but somehow a nice workaround :) Why is there a parameter number limit? – Megaphone

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? – Statehood

This might generate terrible performance with non demo queries that contain joins and filters (and sometimes a complex query error) – Richy

Answer 3

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.

Boeke

Answer 4

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

1. Intersect method
2. 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;

Drilling

Could you give an example how to do it? – Jodoin

The limit is a server limit. What was the resulting query that didn't cause the error? – Richy

Answer 5

If depts is changed to be of type IQueryable<int> the problem will be solved.

You should modify the query that generates depts.

for example use the following code:

var depts = db.departments.Select(d => d.ID);

instead of:

var depts = db.departments.Select(d => d.ID).AsEnumerable();

Walsingham

Answer 6

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

Nitrochloroform

Answer 7

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

Natalianatalie

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. – Richy