Join large list of Integers into LINQ Query

Question

I have LINQ query that returns me the following 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".

All I need is to count all clients that have BirthDate that I have their ID's in list. My list of client ID's could be huge (millions of records).

Here is the query:

List<int> allClients = GetClientIDs();

int total = context.Clients.Where(x => allClients.Contains(x.ClientID) && x.BirthDate != null).Count();

When the query is rewritten this way,

int total = context
    .Clients
    .Count(x => allClients.Contains(x.ClientID) && x.BirthDate != null);

it causes the same error.

Also tried to make it in different way and it eats all memory:

List<int> allClients = GetClientIDs();

total = (from x in allClients.AsQueryable()
         join y in context.Clients
         on x equals y.ClientID
         where y.BirthDate != null
         select x).Count();

Answer 1

We ran into this same issue at work. The problem is that list.Contains() creates a WHERE column IN (val1, val2, ... valN) statement, so you're limited to how many values you can put in there. What we ended up doing was in fact do it in batches much like you did.

However, I think I can offer you a cleaner and more elegant piece of code to do this with. Here is an extension method that will be added to the other Linq methods you normally use:

public static IEnumerable<IEnumerable<T>> BulkForEach<T>(this IEnumerable<T> list, int size = 1000)
{
    for (int index = 0; index < list.Count() / size + 1; index++)
    {
        IEnumerable<T> returnVal = list.Skip(index * size).Take(size).ToList();
        yield return returnVal;
    }
}

Then you use it like this:

foreach (var item in list.BulkForEach())
{
    // Do logic here. item is an IEnumerable<T> (in your case, int)
}  

---

EDIT
Or, if you prefer, you can make it act like the normal List.ForEach() like this:

public static void BulkForEach<T>(this IEnumerable<T> list, Action<IEnumerable<T>> action, int size = 1000)
{
    for (int index = 0; index < list.Count() / size + 1; index++)
    {
        IEnumerable<T> returnVal = list.Skip(index * size).Take(size).ToList();
        action.Invoke(returnVal);
    }
}

Used like this:

list.BulkForEach(p => { /* Do logic */ });