Tuesday, April 12, 2011

LINQ To SQL DatabaseContext Insertion Memory Bloat & Clearing the Cache

Symptom:

You have inserted a huge number of records using the InsertOnSubmit(row) call, and you have ran out of memory in your .NET program using LINQ TO SQL technique.

Cause

The DatabaseContext caches all previously inserted rows.

Solution

There are many articles on the net using the Reflection locate the internal ClearCache then call it. This is not the intended use of the DatabaseContext object. It is a light-weight object and thus, you should simply dispose a object and create a new one. In my situation after a few thousand consecutive insertions, I dispose (i.e., new the object into the variable previously held the old context object.) This is also significantly more efficient way of clearing the cache held.