149

Any ideas on why this could be breaking?

foreach (var p in pp)
{
    ProjectFiles projectFile = (ProjectFiles)p;
    projectFile.Status = Constants.ProjectFiles_ERROR;
    projectFile.DateLastUpdated = DateTime.Now;
    context.SaveChanges();
}

I read that the workaround the issue, is to retrieve the results in one go before the foreach loop.

But didnt I do that? "pp" is the collection of results in my case

H H
  • 263,252
  • 30
  • 330
  • 514
Nick LaMarca
  • 8,076
  • 31
  • 93
  • 152
  • 11
    Is `pp` the result of a linq query? If so, you may need to do a `ToList()` on it to disconnect it from the database before running your loop. – SouthShoreAK Apr 10 '12 at 21:13
  • 2
    call save changes outside loop – Mohsin Jun 23 '14 at 08:56
  • Just a little pointer here. The recommended fix probably works by stopping EF from constantly starting new transactions on each save. However, it points to an issue in EF. The logic here is probably not right. You should start a transaction before saving in a loop, and then commit all, or rollback all at the end. Unless of course this is not the desired logic. – Christian Findlay Nov 27 '17 at 23:06

2 Answers2

376

The pp variable isn't a collection of objects, it's an enumerator that can return objects. While you use the enumerator, the source has to remain open.

Use the ToList method to realise the enumerator into a collection. That will read all items from the enumerator and close the connection to the source, so that you can use the connection for other things.

foreach (var p in pp.ToList())
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • 18
    God bless you @Guffa !!! +1 – NoWar May 21 '13 at 03:09
  • 2
    Great!! It also works with pp.ToArray() – Ricardo Polo Jaramillo Sep 10 '13 at 00:37
  • 2
    Ricardo, it works as long as it is not IQueryable – Null Head Nov 18 '13 at 03:42
  • @Guffa I had the exact same problem and this simple addition worked for me! Thank you! – rikket Jul 22 '14 at 10:03
  • It should be noted that this problem is not specific to Entity Framework. It is related to the underlying SQL Server SqlConnection object. Evidently, in this scenario, Entity framework sees fit to start a transaction for each save. This is a terrible idea, and an argument for not using Entity Framework. My guess is that the code should either commit all changes, or roll them back. This code should be wrapped in a single transaction to achieve this. EF is hiding the transaction handling. – Christian Findlay Nov 27 '17 at 23:05
10

A way to get around this is to call .ToList() on your collection before iterating it.

And while you're at it, call context.SaveChanges() only once after the loop exits to speed up the code.

Captain Kenpachi
  • 6,960
  • 7
  • 47
  • 68
  • 1
    This statement does not seem to describe the real situation, based on the accepted solution's assertions, and those provided in the other answered question, which is linked at the top of this one. It seems more a case of trying to do the change saving before the iteration is complete, rather than the use of multiple connections – theta-fish Apr 04 '18 at 14:37
  • Yeah, there are not multiple connections going on. It has to do with reading from a connection while at the same time trying to write on it. – N73k Jul 01 '20 at 17:04
  • But what if I'm iterating on a million items, I can't load them all into memory. There has to be a way to make updates while also iterating still. – justin.m.chase Feb 18 '21 at 20:30
  • ORM's aren't the way to update millions of records. They are meant for populating complex objects and maybe a bit of CRUD, but bulk operations should be performed directly on the database or at least via a library specifically meant for bulk operations. – Captain Kenpachi Feb 19 '21 at 08:32
  • If you can perform a bulk operation via a stored procedure or scheduled job, then you should ideally want to do that instead. – Captain Kenpachi Feb 19 '21 at 08:39