none
LINQ to Entities: "New transaction is not allowed because there are other threads running in the session." RRS feed

  • Question

  • I have code like the following:

    MyEntities ent = new MyEntities();
    
    var results = (from r in ent.Table
     select r);
    
    foreach (Record r in results)
    {
     r.ValueToUpdate = 5;
     ent.SaveChanges();
    }
    
    

    The problem is that ent.SaveChanges() throws an exception: "New transaction is not allowed because there are other threads running in the session."

    If I move ent.SaveChanges() outside the loop, then it works, but then it will only save once all records are updated.  I want the records to update each at a time.  This pattern works using LINQ to SQL.

    I am using Visual Studio 2010 and ".NET Framework 4 Client Profile".

    Thanks,

    ...Matt

     

    Wednesday, July 14, 2010 9:43 PM

Answers

  • Hi Matt,

     

    Based on my understanding, during the foreach loop, EF still in the original transaction to retrieve all the data records in Table.   So when we call .SaveChanges() to execute the SQL statements in another transaction, we receive the exception.   

     

    To workaround the issue, I would recommend you retrieve the results in one go before the foreach loop.  

    ===========================================================================================

    var results = (from r in ent.Table select r).ToList();

    ===========================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Matt Houser Thursday, July 15, 2010 3:20 PM
    Thursday, July 15, 2010 3:08 AM
    Moderator

All replies

  • Hi Matt,

     

    Based on my understanding, during the foreach loop, EF still in the original transaction to retrieve all the data records in Table.   So when we call .SaveChanges() to execute the SQL statements in another transaction, we receive the exception.   

     

    To workaround the issue, I would recommend you retrieve the results in one go before the foreach loop.  

    ===========================================================================================

    var results = (from r in ent.Table select r).ToList();

    ===========================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Matt Houser Thursday, July 15, 2010 3:20 PM
    Thursday, July 15, 2010 3:08 AM
    Moderator
  • That worked.  Thanks.

    ...Matt

    Thursday, July 15, 2010 3:20 PM
  • You're welcome! 

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 16, 2010 12:55 AM
    Moderator
  • Loading all results into an array isn't good practice for large datasets.

    I posted an answer on Stack Overflow that shows a solution that gets around this problem by working on the data in chunks:

    http://stackoverflow.com/questions/2113498/sqlexception-from-entity-framework-new-transaction-is-not-allowed-because-there/3902790#3902790

    It also allows for reducing the frequency of updates to the database to improve throughput.

    Monday, October 11, 2010 12:23 AM