Linq query list of blob, memory usage

Question

Hello fellow developers,

I'm trying to improve the export of a list of XML objects stored in BLOB format in an Oracle database, using a Linq query.

Sadly, one of the BLOB's is quite big and the memory usage grows up to 2 GB when I'm reading it. My fileSet object is an IQueryable<myRecord> object. I tried

foreach (var file in fileSet){...}

and

var files = fileSet.ToList(); //This time the list is causing the memory load.
foreach(file in files){...}

and

var e = fileSet.AsEnumerable().GetEnumerator();
while(e.MoveNext()){...}

But every time I hit the big record in the list, the ram is over used. For the creation of the export I was looking for some code using Buffer.BlockCopy but because of the memory overcharge there is no point going further in this direction, if you have some idea how to reduce the memory usage or to lazy load each blob :(

Answer 1

There're several solutions: 1) add AsNoTracking() to your query.

fileSet.AsNoTracking() or fileSet.AsNoTracking().Where(...) 

AsNoTracking() helps the garbage collector to free records because the records will not be cached in the database context. But as you know it does not act immediately, you still may have a local increase in consumed memory.

2) you may create a separate definition of your record that does not include the blob field and get the list of files by it or use a select expression it may help also but you should check how it is translated to the sql

fileSet.AsNoTracking().Select(x=>new { x.Id, x.Name })

Then processing each record you will be explicitly get a blob

var myblob = model.Database
    .SqlQuery<string>("select myblob from mytable where id=@id", 
         new SqlParameter("@id", System.Data.SqlDbType.Int) { Value = myId })
    .FirstOrDefault();

or

var myBlob = fileSet
       .AsNoTracking()
       .Select(x=>new { x.Blob )
       .FirstOrDefault(x=>x.ConfigId=myId);

Answer 2

Finally because I couldn't manage to have something light in memory with linq, I used an OracleCommand to stream the xml file, after I've got the ID of the file with linq.

using (var reader = cmd.ExecuteReader())
{
   while (reader.Read())
   {
      var blob = reader.GetOracleLob(0);
      var buffer = new byte[128];
      using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.Write))
      {
          blob.CopyTo(fs);
      }
   }
}