2

I have this simple code :

foreach(var myvar in _sdb.Vars)
{
    myvar.Area= AreaCheck(myvar);

    _sdb.Entry(myvar).State = System.Data.Entity.EntityState.Modified;
    _sdb.SaveChanges();
}

When I run this code I get this exception :

New transaction is not allowed because there are other threads running in the session.

I googled the problem, one of the answers told me to change this line : 

foreach(var myvar in _sdb.Vars)

To this line : 

foreach(var myvar in _sdb.Vars.ToList())

But when I did it, the code didn't work at all. How can I make this work? Thanks.

jason
  • 6,962
  • 36
  • 117
  • 198
  • 1
    `var myvar in _sdb.Vars` will throw error as `sdb.Vars` is `IQueryable` and you are trying to modify the state of it. `foreach(var myvar in _sdb.Vars.ToList())` should work but depends on what you are doing inside `AreaCheck(myvar)` method. Please add that code as well. Also, move the `_sdb.SaveChanges();` outside the `foreach` loop to avoid multiple database hits – Developer Nov 14 '16 at 05:44
  • "But when I did it, the code didn't work at all" - didn't work how? – Evk Nov 14 '16 at 06:09

1 Answers1

4

Save after iterations like below 

using (var _sdb = new MyContext())
{
    foreach (var myvar in _sdb.Vars)
    {
        // Change myvar
    }
     //save at the end
    _sdb.SaveChanges();
}
Damith
  • 62,401
  • 13
  • 102
  • 153
  • There are 300000 records in the database. Would this still work? – jason Nov 14 '16 at 06:04
  • This will work for small data sets, if this is large data set, then you better fetch them as chunks.. Check this asnwer http://stackoverflow.com/a/3902790/2558060 – Damith Nov 14 '16 at 06:09
  • Or simply don't use ORMs for that kind of thing. Just tell your database server by using something like stored procedure. – CodeNotFound Nov 14 '16 at 06:35
  • 2
    @CodeNotFound Yes, that will be a better solution but we don't know the exact requirements of OP. If OP depending more on ORM data when updating and can't move everything to db side in this situation or with the timeline then we have to try one of these – Damith Nov 14 '16 at 06:41