[EF4]New transaction is not allowed because there are other threads running in the session

I got “New transaction is not allowed because there are other threads running in the session”while trying to delete some records via EF4.

The code looks like the following.

  1: using (var db = new MyDB()) {
  2: 	var ds = db.MyTable.Where(x => x.EndTime.Value <= DateTime.UtcNow);
  3: 	foreach (var d in ds) {
  4: 	    db.MyTable.DeleteObject(d);
  5: 	    db.SaveChanges();
  6: 	}
  7: }

One thing we need to know about Entity Framework is that when you iterate results in a loop such as “foreach”, it actually creates a open reader to iterate data for you. So back to the codes, since we are still drawing data via an active reader, then obviously we cannot “SaveChanges” when the connection is still in use. What we need to do here is to force EF to return all records, after that, we can do the “SaveChanges” call. So simply force to retrieve all data via invoking “ToList” on the entity.

  1: using (var db = new MyDB()) {
  2: 	var ds = db.MyTable.Where(x => x.EndTime.Value <= DateTime.UtcNow).ToList();
  3: 	foreach (var d in ds) {
  4: 	    db.MyTable.DeleteObject(d);
  5: 	    db.SaveChanges();
  6: 	}
  7: }