How to prevent memory overflow when using an IEnumerable<T> and Linq-To-Sql?

Question

This question is related to a previous question of mine

That's my current code

 IEnumerable<Shape> Get()
 {
     while(//get implementation
         yield return new Shape(//...
 }

 void Insert()
 {
       var actual = Get();
       using (var db = new DataClassesDataContext())
       {
           db.Shapes.InsertAllOnSubmit(actual);
           db.SubmitChanges();
       }
 }

I'm getting a memory overflow, since the IEnumerable is too big. How do I prevent it?

Answer 1

One option is to break it up into multiple batches. Create a temporary buffer of Shape objects, iterate until you fill it or run out from the enumerator, then do a InsertBatchOnSubmit.

Answer 2

Try using InsertOnSubmit rather than InsertAllOnSubmit. And then commit at appropriate intervals, like Erich said.

Or, if you want to do it in batches of e.g. 5, try Handcraftsman's or dtb's solutions for getting IEnumerable's of IEnumerable. E.g., with dtb's Chunk:

   var actual = Get();
   using (var db = new DataClassesDataContext())
   {
       foreach(var batch in actual.Chunk(5))
       {
         db.Shapes.InsertAllOnSubmit(batch);
         db.SubmitChanges();
       }
   }

Answer 3

For a neat way to get batches of items from an IEnumerable, see this:

http://stackoverflow.com/questions/1008785/c-cleanest-way-to-divide-a-string-array-into-n-instances-n-items-long/1008974#1008974

Update: No good, that works on arrays. If I have some time later and no one else has provided something, I'll write it up...

Answer 4

Use the following extension method to break the input into appropriately sized subsets

public static class IEnumerableExtensions
{
    public static IEnumerable<List<T>> InSetsOf<T>(this IEnumerable<T> source, int max)
    {
     List<T> toReturn = new List<T>();
     foreach(var item in source)
     {
      toReturn.Add(item);
      if (toReturn.Count == max)
      {
       yield return toReturn;
       toReturn = new List<T>();
      }
     }
     if (toReturn.Any())
     {
      yield return toReturn;
     }
    }
}

then persist the subsets

void Insert()
{
    var actual = Get();
    using (var db = new DataClassesDataContext())
    {
     foreach (var set in actual.InSetsOf(5))
     {
      db.Shapes.InsertAllOnSubmit(set);
      db.SubmitChanges();
     }
    }
}

You might also find this MSDN article on InsertOnSubmit() vs InsertAllOnSubmit() to be useful.