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

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
ban-G
  • 1,045
  • 2
  • 10
  • 15
  • 1
    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
  • did you tried any solution? not marked none – Kiquenet Mar 13 '17 at 19:34
  • 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

5 Answers5

16

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;
ADM-IT
  • 3,017
  • 21
  • 21
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.

Joel
  • 18,947
  • 2
  • 62
  • 82
1

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.

jpierson
  • 15,160
  • 11
  • 100
  • 144
1

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;
Roman O
  • 2,654
  • 25
  • 25
-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

Community
  • 1
  • 1
btt
  • 422
  • 6
  • 16