CODEWITHSUNDEEP
c#asp.netasp.net-mvc
Niklas
Niklas

Reputation: 13135

Split query into multiple queries and then join the results

I have this function below that takes a list of id's and searches the DB for the matching persons. 

public IQueryable<Person> GetPersons(List<int> list)
{
    return db.Persons.Where(a => list.Contains(a.person_id));
}

The reason I need to split this into four queries is because the query can't take more than 2100 comma-separated values:
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.

How can I split the list into 4 pieces and make a query for each list. Then join the results into one list of persons?

Solved
I don't want to post it as an own answer and take cred away from @George Duckett's answer, just show the solution: 

public IQueryable<Person> GetPersons(List<int> list)
    {
        var persons = Enumerable.Empty<Person>().AsQueryable<Person>();
        var limit = 2000;
        var result = list.Select((value, index) => new { Index = index, Value = value })
                 .GroupBy(x => x.Index / limit)
                 .Select(g => g.Select(x => x.Value).ToList())
                 .ToList();

        foreach (var r in result)
        {
            var row = r;
            persons = persons.Union(db.Persons.Where(a => row.Contains(a.person_id)));
        }
        return persons;
    }

Upvotes: 0

Views: 2444

Answers (2)

lahsrah
lahsrah

Reputation: 9173

I am not sure why you have a method like this. What exactly are you trying to do. Anyway you can do it with Skip and Take methods that are used for paging.

List<Person> peopleToReturn = new List<Person>();

int pageSize = 100;

var idPage = list.Skip(0).Take(pageSize).ToList();

int index = 1;

while (idPage.Count > 0)
{
    peopleToReturn.AddRange(db.Persons.Where(a => idPage.Contains(a.person_id)).ToList());
    idPage = list.Skip(index++ * pageSize).Take(pageSize).ToList();
}

Upvotes: 1

George Duckett
George Duckett

Reputation: 32428

See this answer for splitting up your list: Divide a large IEnumerable into smaller IEnumerable of a fix amount of item

var result = list.Select((value, index) => new { Index = index, Value = value})
              .GroupBy(x => x.Index / 5)
              .Select(g => g.Select(x => x.Value).ToList())
              .ToList();

Then do a foreach over the result (a list of lists), using the below to combine them.

See this answer for combining the results: How to combine Linq query results

Upvotes: 3

Related Questions