OutOfMemoryException: using DbContext in Task

Question

This is the following setup I'm currently dealing with:

• I got a sql-table with ~1.000.000 entries I need to update
• the update takes place in a seperate thread (started by a task)
• as the memory is limited in a thread, i process the list in batches of 1000 entries per batch (when running this in a test-project in the mainthread/without any task, there is no OOM exception)
• the UpdateList() function either updates fields of the list or creates new record for this or other tables in the DbContext
• in the Process_Failure() function, I have a single context instance for the entire list
• in the Process_Success() function, I moved the while-loop outside of the context

---

private void Process_Success()
{
    var totalProcessedCounter = 0;

    while( true )
    {
        using( var context = new MyDbContext() )
        {
            var list = context.MyClass.OrderBy( x => x.Id )
                .Skip( totalProcessedCounter ).Take( 1000 )
                .ToList();

            if( !list.Any() )
                break;

            UpdateList( list );

            totalProcessedCounter += list.Count;
        }
    }
}

private void Process_Failure()
{
    var totalProcessedCounter = 0;

    using( var context = new MyDbContext() )
    {
        while( true )
        {
            var list = context.MyClass.OrderBy( x => x.Id )
                .Skip( totalProcessedCounter ).Take( 1000 )
                .ToList(); // throws OutOfMemoryException

            if( !list.Any() )
                break;

            UpdateList( list );

            totalProcessedCounter += list.Count;
        }
    }
}

private void UpdateList( List<MyClass> list )
{
    var doSaveChanges = false;

    list = list.Where( x => SomeFilter( x ) ).ToList();

    using( var context = new MyDbContext() )
    {
        foreach( var item in list )
        {
            ChangeItem( item );
        }

        if( doSaveChanges )
            context.SaveChanges();
    }
}

Gets the context somehow polluted/filled when I create another instance in the nested functioncall of UpdateList()?

Answer 1

The reason why you get the exception is because DbContext caches the data you read from DB, therefore at some point you will blow up your memory if you keep adding entities to its cache and you will get the OutOfMemoryException. Entities are not cleared by the GC because they are being referenced by the DbContext.

Try using .AsNoTracking():

private void Process_NoTracking()
{
    var totalProcessedCounter = 0;

    using( var context = new MyDbContext() )
    {
        while( true )
        {
            var list = context
                          .MyClass
                          .AsNoTracking()
                          .OrderBy( x => x.Id )
                          .Skip( totalProcessedCounter )
                          .Take( 1000 )
                          .ToList(); 

            if( !list.Any() )
                break;

            UpdateList( list );

            totalProcessedCounter += list.Count;
        }
    }
}

But if you do not track the entities, updating them is more difficult (read "Attaching an existing entity to the context"), because those entities do not belong to any context and they are not being tracked.

I would not use EF for somethng like this, this looks like a good mission for an UPDATE/SELECT SQL statement.