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

Solution 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;

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

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

Solution 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;