Does the linq enumerator stream? (memory bloat problem)

[wtftc]

Hi,

I was wondering if the devart linq enumerator streams (I am using the MySql one). For example, when I do:

Code: Select all

foreach (var item in from mt in DataContext.MyTable select mt)
{
    Console.Out.WriteLine(item);
}

does the full MyTable get loaded into memory before iteration starts? Or does only one row at a time get loaded into memory as I print them out.

The reason I am asking is because I am trying to iterate over a large table and it looks like I have a huge memory spike in the foreach statement, which is highly undesirable. It would be much better to load one row at a time, as I would expect with an enumerator. If I called ToList() on the query, then it would be acceptable to form an in memory list of everything, but not until then.

[StanislavK]

When using the foreach statement, only one row at once should be loaded. The problem with memory consumption may be caused, e.g., by setting DataLoadOptions of the data context: if entities related to DataContext.MyTable ('details', supposed that MyTable is 'master' in a master/detail relation) are eagerly loaded, each entity from MyTable will be held in memory with several 'detail' entities.

If no data load options are set, please describe the scenario you are trying to perform in more details. If possible, send us a test project with which the problem may be reproduced.

Also, it may be helpful to check the SQL commands generated for your LINQ queries. You can use the LinqMonitor component and dbMonitor application for this:
http://www.devart.com/linqconnect/docs/dbMonitor.html

[wtftc]

I am writing this for the benefit of the rest of the forum users.

It looks like the best way to do this is to set ObjectTrackingEnabled to false on the DataContext you are using to read a large table. Here is the msdn documentation and here is a well written blog post on the intricacies of objects and state tracking in LINQ to SQL.

Setting DataContext.ObjectTrackingEnable = False makes that DataContext read-only. If you do need to modify some of those rows that you just read, you must create a second DataContext and call Attach on the writeable DataContext.

Code: Select all

readContext.ObjectTrackingEnabled = false;
int i = 0;
foreach (var item in from row in readContext.MyLargeTable select row)
{
    if (condition[i])
    {
        //if our condition is true, we track this primary key
        writeContext.TrackingTable.InsertOnSubmit(new TrackingTable(row.PrimaryKey));
        writeContext.SubmitChanges();
    }
    else
    {
        //otherwise we must null out a property on this table
        writeContext.MyLargeTable.Attach(row);
        row.MyProperty = null;
        writeContext.SubmitChanges();
    }
    i++;
}

[StanislavK]

Thank you for sharing this information.