IQueryable<T> extension method to take data in batches

Question

Has anyone found/coded extension method that query data (using linq to sql) in batches? I've seen IEnumerable extensions but I'm looking for something I might use like this:

IQueryable<Order> orders = from i in db.Orders select i;
foreach(var batch in orders.InBatches(100))
{
   //batch of 100 products
   foreach(var order in batch)
   {
      //do something
   }
}

Answer 1

What you can do is this:

public static IEnumerable<IQueryable<T>> InBatches(
    this IQueryable<T> collection, int size)
{  
    int totalSize = collection.Count();

    for (int start = 0; start < totalSize; start += size)
    {
        yield return collection.Skip(start).Take(size);
    }
}

This extension method allows you to do extra filters over the return IQueryables. However, the usefulness is pretty limited. I can't think of any good scenario for this :-). In most scenario's you just want to stream the results and returning an IEnumerable<IEnumerable<T>> would just do fine, and is even better, since this will result in a single SQL query, while the shown approach will result in N + 1 queries.

Answer 2

What's wrong with Take and Skip? These are the LINQ operators for getting batches off an IEnumerable<T> or IQueryable<T> (and their non-generic counterparts).

Answer 3

If you don't care about the batches themselves and you just want to break up connections for size or transaction purposes you can do the following:

public static IEnumerable<T> InBatches<T>(this IQueryable<T> collection, int batchSize)
{
    int start = 0;
    int records = 0;
    IQueryable<T> batch;

    // For the first batch
    start -= batchSize;

    do {
        records = 0;
        start += batchSize;
        batch = collection.Skip(start).Take(batchSize);

        foreach (T item in batch) {

            records += 1;
            yield return item;
        }

    } while (records == batchSize);
}